从SQL Server函数返回多个值

How would I return multiple values (say, a number and a string) from a user-defined function in SQL Server?

如何从SQL Server中的用户定义函数返回多个值(例如,数字和字符串)?

5 个解决方案

#1

make it a table-valued function

使它成为一个表值函数

see here http://technet.microsoft.com/en-us/library/ms191165.aspx , example included

请参阅http://technet.microsoft.com/en-us/library/ms191165.aspx,其中包含示例

#2

Another option would be to use a procedure with output parameters - Using a Stored Procedure with Output Parameters

另一种选择是使用带有输出参数的过程 - 使用带有输出参数的存储过程

#3

Erland Sommarskog has an exhaustive post about passing data in SQL Server located here:

Erland Sommarskog有一篇关于在SQL Server中传递数据的详尽文章:

http://www.sommarskog.se/share_data.html

He covers SQL Server 2000, 2005, and 2008, and it should probably be read in its full detail as there is ample coverage of each method's advantages and drawbacks. However, here are the highlights of the article (frozen in time as of July 2015) for the sake of providing search terms that can be used to look greater details:

他介绍了SQL Server 2000,2005和2008,并且应该详细阅读它,因为每种方法的优点和缺点都有充分的内容。但是,为了提供可用于查看更多详细信息的搜索术语,以下是本文的重点(截至2015年7月为时冻结):

This article tackles two related questions:

本文解决了两个相关问题:

• How can I use the result set from one stored procedure in another, also expressed as How can I use the result set from a stored
  procedure in a SELECT statement?

如何在另一个存储过程中使用结果集,也表示为如何在SELECT语句中使用存储过程的结果集?

• How can I pass a table data in a parameter from one stored procedure to another?

如何将参数中的表数据从一个存储过程传递到另一个存储过程?

OUTPUT Parameters

• Not generally applicable, but sometimes overlooked.

通常不适用,但有时会被忽视。

Table-valued Functions

• Often the best choice for output-only, but there are several restrictions.

通常是仅输出的最佳选择,但有几个限制。

• Examples:
  • Inline Functions: Use this to reuse a single SELECT.

  内联函数:使用此函数重用单个SELECT。

  • Multi-statement Functions: When you need to encapsulate more complex logic.

  多语句函数:当您需要封装更复杂的逻辑时。

示例:内联函数:使用此函数重用单个SELECT。多语句函数:当您需要封装更复杂的逻辑时。

Using a Table

使用表格

• The most general solution. My favoured choice for input/output scenarios.

最通用的解决方案。我最喜欢的输入/输出场景选择。

• Examples:
  • Sharing a Temp Table: Mainly for a single pair of caller/callee.

  共享临时表:主要用于一对呼叫者/被呼叫者。

  • Process-keyed Table: Best choice for many callers to the same callee.

  进程密钥表:同一被调用者的许多呼叫者的最佳选择。

  • Global Temp Tables: A variation of process-keyed.

  全局临时表:流程键的变体。

示例:共享临时表:主要用于一对呼叫者/被呼叫者。进程密钥表:同一被调用者的许多呼叫者的最佳选择。全局临时表:流程键的变体。

Table-valued Parameters

• Req. Version: SQL 2008

所需物品。版本:SQL 2008

• Mainly useful when passing data from a client.

从客户端传递数据时非常有用。

INSERT-EXEC

• Deceivingly appealing, but should be used sparingly.

欺骗性的吸引力,但应谨慎使用。

Using the CLR

使用CLR

• Req. Version: SQL 2005

所需物品。版本:SQL 2005

• Complex, but useful as a last resort when INSERT-EXEC does not work.

复杂,但在INSERT-EXEC不起作用时作为最后的手段很有用。

OPENQUERY

• Tricky with many pitfalls. Discouraged.

有许多陷阱的棘手。泄气。

Using XML

• Req. Version: SQL 2005

所需物品。版本:SQL 2005

• A bit of a kludge, but not without advantages.

有点像kludge,但并非没有优势。

Using Cursor Variables

使用光标变量

• Not recommendable.

#4

Here's the Query Analyzer template for an in-line function - it returns 2 values by default:

这是内联函数的查询分析器模板 - 默认情况下返回2个值:

-- =============================================  
-- Create inline function (IF)  
-- =============================================  
IF EXISTS (SELECT *   
   FROM   sysobjects   
   WHERE  name = N'<inline_function_name, sysname, test_function>')  
DROP FUNCTION <inline_function_name, sysname, test_function>  
GO  

CREATE FUNCTION <inline_function_name, sysname, test_function>   
(<@param1, sysname, @p1> <data_type_for_param1, , int>,   
 <@param2, sysname, @p2> <data_type_for_param2, , char>)  
RETURNS TABLE   
AS  
RETURN SELECT   @p1 AS c1,   
        @p2 AS c2  
GO  

-- =============================================  
-- Example to execute function  
-- =============================================  
SELECT *   
FROM <owner, , dbo>.<inline_function_name, sysname, test_function>   
    (<value_for_@param1, , 1>,   
     <value_for_@param2, , 'a'>)  
GO  

#5

Example of using a stored procedure with multiple out parameters

As User Mr. Brownstone suggested you can use a stored procedure; to make it easy for all i created a minimalist example. First create a stored procedure:

用户布朗斯通先生建议您可以使用存储过程;让我轻松创造一个极简主义的例子。首先创建一个存储过程:

Create PROCEDURE MultipleOutParameter
    @Input int,
    @Out1 int OUTPUT, 
    @Out2 int OUTPUT 
AS
BEGIN
    Select @Out1 = @Input + 1
    Select @Out2 = @Input + 2   
    Select 'this returns your normal Select-Statement' as Foo
          , 'amazing is it not?' as Bar

    -- Return can be used to get even more (afaik only int) values 
    Return(@Out1+@Out2+@Input)
END 

Calling the stored procedure

To execute the stored procedure a few local variables are needed to receive the value:

要执行存储过程,需要一些局部变量来接收值:

DECLARE @GetReturnResult int, @GetOut1 int, @GetOut2 int 
EXEC @GetReturnResult = MultipleOutParameter  
    @Input = 1,
    @Out1 = @GetOut1 OUTPUT,
    @Out2 = @GetOut2 OUTPUT

To see the values content you can do the following

要查看值内容,您可以执行以下操作

Select @GetReturnResult as ReturnResult, @GetOut1 as Out_1, @GetOut2 as Out_2 

This will be the result:

这将是结果: