How would I return multiple values (say, a number and a string) from a user-defined function in SQL Server?
This question is related to
tsql
user-defined-functions
The answer is
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
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:
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
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.
Another option would be to use a procedure with output parameters - Using a Stored Procedure with Output Parameters
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
Another option would be to use a procedure with output parameters - Using a Stored Procedure with Output Parameters
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:
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.
Source: Stackoverflow.com