SQL Server 存储过程返回一个表

SQL server stored procedure return a table

提问by mHelpMe

I have a stored procedure that takes in two parameters. I can execute it successfully in Server Management Studio. It shows me the results which are as I expect. However it also returns a Return Value.

我有一个接受两个参数的存储过程。我可以在 Server Management Studio 中成功执行它。它向我展示了我所期望的结果。但是它也会返回一个返回值。

It has added this line,

它添加了这一行，

 SELECT 'Return Value' = @return_value

I would like the stored procedure to return the table it shows me in the results not the return value as I am calling this stored procedure from MATLAB and all it returns is true or false.

我希望存储过程返回它在结果中显示的表，而不是返回值，因为我从 MATLAB 调用这个存储过程，它返回的所有内容都是真或假。

Do I need to specify in my stored procedure what it should return? If so how do I specify a table of 4 columns (varchar(10), float, float, float)?

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

回答by t-clausen.dk

A procedure can't return a table as such. However you can select from a table in a procedure and direct it into a table (or table variable) like this:

过程不能像这样返回表。但是，您可以从过程中的表中进行选择，并将其定向到表（或表变量）中，如下所示：

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

回答by rainabba

I do this frequently using Table Types to ensure more consistency and simplify code. You can't technically return "a table", but you can return a result set and using INSERT INTO .. EXEC ...syntax, you can clearly call a PROC and store the results into a table type. In the following example I'm actually passing a table into a PROC along with another param I need to add logic, then I'm effectively "returning a table" and can then work with that as a table variable.

我经常使用表类型来确保更高的一致性并简化代码。从技术上讲，您不能返回“表”，但可以返回结果集并使用INSERT INTO .. EXEC ...语法，您可以清楚地调用 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

回答by Algonaut

Consider creating a function which can return a table and be used in a query.

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

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

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

The main difference between a function and a procedure is that a function makes no changes to any table. It only returns a value.

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

In this example I'm creating a query to give me the counts of all the columns in a given table which aren't null or empty.

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

There are probably many ways to clean this up. But it illustrates a function well.

可能有很多方法可以清理它。但它很好地说明了一个功能。

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

Then executing the function with

然后用

SELECT * FROM usp_listFields('Employees')

produces a number of rows like:

产生许多行，如：

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

回答by Cassio Veras

I had a similar situation and solved by using a temp table inside the procedure, with the same fields being returned by the original Stored Procedure:

我遇到了类似的情况并通过在过程中使用临时表来解决，原始存储过程返回相同的字段：

CREATE PROCEDURE mynewstoredprocedure
AS 
BEGIN

INSERT INTO temptable (field1, field2)
EXEC mystoredprocedure @param1, @param2

select field1, field2 from temptable

-- (mystoredprocedure returns field1, field2)

END

回答by HasaniH

You can use an out parameter instead of the return value if you want both a result set and a return value

如果同时需要结果集和返回值，则可以使用 out 参数代替返回值

CREATE PROCEDURE proc_name 
@param int out
AS
BEGIN
    SET @param = value
SELECT ... FROM [Table] WHERE Condition
END
GO

回答by John Eisbrener

The Status Value being returned by a Stored Procedure can only be an INT datatype. You cannot return other datatypes in the RETURN statement.

存储过程返回的状态值只能是 INT 数据类型。您不能在 RETURN 语句中返回其他数据类型。

From Lesson 2: Designing Stored Procedures:

从第 2 课：设计存储过程：

Every stored procedure can return an integervalue known as the execution status value or return code.

每个存储过程都可以返回一个整数值，称为执行状态值或返回代码。

If you still want a table returned from the SP, you'll either have to work the record set returned from a SELECT within the SP or tie into an OUTPUT variable that passes an XML datatype.

如果您仍然需要从 SP 返回的表，则必须处理从 SP 中的 SELECT 返回的记录集，或者绑定到传递 XML 数据类型的 OUTPUT 变量。

HTH,

哈，

John

约翰

回答by Rohit

create procedure PSaleCForms
as
begin
declare 
@b varchar(9),
@c nvarchar(500),
@q nvarchar(max)
declare @T table(FY nvarchar(9),Qtr int,title nvarchar    (max),invoicenumber     nvarchar(max),invoicedate datetime,sp decimal    18,2),grandtotal decimal(18,2))
declare @data cursor
set @data= Cursor
forward_only static
for 
select x.DBTitle,y.CurrentFinancialYear from [Accounts     Manager].dbo.DBManager x inner join [Accounts Manager].dbo.Accounts y on        y.DBID=x.DBID where x.cfy=1
open @data
fetch next from @data
into @c,@b
while @@FETCH_STATUS=0
begin
set @q=N'Select '''+@b+''' [fy], case cast(month(i.invoicedate)/3.1 as int)     when 0 then 4 else cast(month(i.invoicedate)/3.1 as int) end [Qtr],     l.title,i.invoicenumber,i.invoicedate,i.sp,i.grandtotal from     ['+@c+'].dbo.invoicemain i inner join  ['+@c+'].dbo.ledgermain l on     l.ledgerid=i.ledgerid where (sp=0 or stocktype=''x'') and invoicetype=''DS'''

insert into @T exec [master].dbo.sp_executesql @q fetch next from @data into @c,@b end close @data deallocate @data select * from @T return end

插入@T exec [master].dbo.sp_executesql @q fetch next from @data into @c,@b end close @data deallocate @data select * from @T return end

回答by RizKhan

Though this question is very old but as a new in Software Development I can't stop my self to share what I have learnt :D

虽然这个问题很老，但作为软件开发中的一个新问题，我无法阻止自己分享我所学到的：D

Creation of Stored Procedure:

存储过程的创建：

CREAET PROC usp_ValidateUSer
(
    @UserName nVARCHAR(50),
    @Password nVARCHAR(50)
)
AS

BEGIN
    IF EXISTS(SELECT '#' FROM Users WHERE Username=@UserName AND Password=@Password)
    BEGIN
        SELECT u.UserId, u.Username, r.UserRole
        FROM Users u
        INNER JOIN UserRoles r
        ON u.UserRoleId=r.UserRoleId
    END
END

Execution of Stored Procedure:

存储过程的执行：

(If you want to test the execution of Stored Procedure in SQL)

（如果要测试SQL中Stored Procedure的执行情况）

EXEC usp_ValidateUSer @UserName='admin', @Password='admin'

Th Output:

输出：

回答by SteveB

Here's an example of a SP that both returns a table and a return value. I don't know if you need the return the "Return Value" and I have no idea about MATLAB and what it requires.

下面是一个同时返回表和返回值的 SP 示例。我不知道您是否需要返回“返回值”，我不知道 MATLAB 及其需要什么。

CREATE PROCEDURE test
AS 
BEGIN

    SELECT * FROM sys.databases

    RETURN 27
END

--Use this to test
DECLARE @returnval int

EXEC @returnval = test 

SELECT @returnval