SQL server存储过程返回一个表

Question

我有一个包含两个参数的存储过程。我可以在服务器管理工作室成功执行它。它向我显示了我所期望的结果。然而，它也返回一个返回值。

它增加了这一行，

 SELECT 'Return Value' = @return_value

我希望存储过程返回它在结果中显示给我的表，而不是返回值，因为我从MATLAB调用这个存储过程，它返回的都是true或false。

我需要在存储过程中指定它应该返回什么吗？如果是这样，我如何指定一个包含4列的表（varchar（10）、float、float、float）？

Answer 1

考虑创建一个可以返回表并在查询中使用的函数。

https://msdn.microsoft.com/en-us/library/ms186755.aspx

函数和过程之间的主要区别在于，函数不会对任何表进行更改。它只返回一个值。

在本例中，我创建了一个查询，以提供给定表中所有非空列的计数。

可能有很多方法可以解决这个问题。但它很好地说明了一个函数。

USE Northwind

CREATE FUNCTION usp_listFields(@schema VARCHAR(50), @table VARCHAR(50))
RETURNS @query TABLE (
    FieldName VARCHAR(255)
    )
BEGIN
    INSERT @query
    SELECT
        'SELECT ''' + @table+'~'+RTRIM(COLUMN_NAME)+'~''+CONVERT(VARCHAR, COUNT(*)) '+
    'FROM '+@schema+'.'+@table+' '+
          ' WHERE isnull("'+RTRIM(COLUMN_NAME)+'",'''')<>'''' UNION'
    FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @table and TABLE_SCHEMA = @schema
    RETURN
END

然后用

SELECT * FROM usp_listFields('Employees')

生成许多行，如：

SELECT 'Employees~EmployeeID~'+CONVERT(VARCHAR, COUNT(*)) FROM dbo.Employees  WHERE isnull("EmployeeID",'')<>'' UNION
SELECT 'Employees~LastName~'+CONVERT(VARCHAR, COUNT(*)) FROM dbo.Employees  WHERE isnull("LastName",'')<>'' UNION
SELECT 'Employees~FirstName~'+CONVERT(VARCHAR, COUNT(*)) FROM dbo.Employees  WHERE isnull("FirstName",'')<>'' UNION

Answer 2

我经常使用表类型来确保一致性并简化代码。从技术上讲，您不能返回“一个表”，但可以返回一个结果集，并使用INSERT-INTO。。执行官 语法，您可以清楚地调用PROC并将结果存储到表类型中。在下面的例子中，我实际上是将一个表和另一个需要添加逻辑的参数一起传递到PROC中，然后我有效地“返回一个表”，然后可以将其作为表变量使用。

/****** Check if my table type and/or proc exists and drop them ******/
IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'returnTableTypeData')
DROP PROCEDURE returnTableTypeData
GO
IF EXISTS (SELECT * FROM sys.types WHERE is_table_type = 1 AND name = 'myTableType')
DROP TYPE myTableType
GO

/****** Create the type that I'll pass into the proc and return from it ******/
CREATE TYPE [dbo].[myTableType] AS TABLE(
    [someInt] [int] NULL,
    [somenVarChar] [nvarchar](100) NULL
)
GO

CREATE PROC returnTableTypeData
    @someInputInt INT,
    @myInputTable myTableType READONLY --Must be readonly because
AS
BEGIN

    --Return the subset of data consistent with the type
    SELECT
        *
    FROM
        @myInputTable
    WHERE
        someInt < @someInputInt

END
GO


DECLARE @myInputTableOrig myTableType
DECLARE @myUpdatedTable myTableType

INSERT INTO @myInputTableOrig ( someInt,somenVarChar )
VALUES ( 0, N'Value 0' ), ( 1, N'Value 1' ), ( 2, N'Value 2' )

INSERT INTO @myUpdatedTable EXEC returnTableTypeData @someInputInt=1, @myInputTable=@myInputTableOrig

SELECT * FROM @myUpdatedTable


DROP PROCEDURE returnTableTypeData
GO
DROP TYPE myTableType
GO

Answer 3

过程不能返回这样的表。但是，您可以从过程中的表格中进行选择，并将其导入如下表格（或表格变量）：

create procedure p_x
as
begin
declare @t table(col1 varchar(10), col2 float, col3 float, col4 float)
insert @t values('a', 1,1,1)
insert @t values('b', 2,2,2)

select * from @t
end
go

declare @t table(col1 varchar(10), col2 float, col3 float, col4 float)
insert @t
exec p_x

select * from @t