Return multiple values from a SQL Server function

Question

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

Answer 1

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:

Answer 2

make it a table-valued function

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

Answer 3

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

-- =============================================  
-- 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  

Answer 4

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

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:

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?
• 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.
  • Multi-statement Functions: When you need to encapsulate more complex logic.

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
• Mainly useful when passing data from a client.

INSERT-EXEC

• Deceivingly appealing, but should be used sparingly.

Using the CLR

• Req. Version: SQL 2005
• Complex, but useful as a last resort when INSERT-EXEC does not work.

OPENQUERY

• Tricky with many pitfalls. Discouraged.

Using XML

• Req. Version: SQL 2005
• A bit of a kludge, but not without advantages.

Using Cursor Variables

• Not recommendable.

Answer 5

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