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

User · Answer

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  lt inline function name  sysname  test function gt      DROP FUNCTION  lt inline function name  sysname  test function gt    GO    CREATE FUNCTION  lt inline function name  sysname  test function gt       lt  param1  sysname   p1 gt   lt data type for param1    int gt        lt  param2  sysname   p2 gt   lt data type for param2    char gt     RETURNS TABLE    AS   RETURN SELECT    p1 AS c1              p2 AS c2   GO    --                                                 -- Example to execute function   --                                                 SELECT      FROM  lt owner    dbo gt   lt inline function name  sysname  test function gt           lt value for  param1    1 gt            lt value for  param2     a  gt     GO

User · Answer

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

User · Answer

Change it to a table-valued function Please refer to the following link  for example

User · Answer

Change it to a table-valued function Please refer to the following link  for example

User · Answer

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

User · Answer

Change it to a table-valued function Please refer to the following link  for example

User · Answer

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

User · Answer

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  lt inline function name  sysname  test function gt      DROP FUNCTION  lt inline function name  sysname  test function gt    GO    CREATE FUNCTION  lt inline function name  sysname  test function gt       lt  param1  sysname   p1 gt   lt data type for param1    int gt        lt  param2  sysname   p2 gt   lt data type for param2    char gt     RETURNS TABLE    AS   RETURN SELECT    p1 AS c1              p2 AS c2   GO    --                                                 -- Example to execute function   --                                                 SELECT      FROM  lt owner    dbo gt   lt inline function name  sysname  test function gt           lt value for  param1    1 gt            lt value for  param2     a  gt     GO

User · Answer

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

User · Answer

Change it to a table-valued function Please refer to the following link  for example

User · Answer

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  lt inline function name  sysname  test function gt      DROP FUNCTION  lt inline function name  sysname  test function gt    GO    CREATE FUNCTION  lt inline function name  sysname  test function gt       lt  param1  sysname   p1 gt   lt data type for param1    int gt        lt  param2  sysname   p2 gt   lt data type for param2    char gt     RETURNS TABLE    AS   RETURN SELECT    p1 AS c1              p2 AS c2   GO    --                                                 -- Example to execute function   --                                                 SELECT      FROM  lt owner    dbo gt   lt inline function name  sysname  test function gt           lt value for  param1    1 gt            lt value for  param2     a  gt     GO

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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  lt inline function name  sysname  test function gt      DROP FUNCTION  lt inline function name  sysname  test function gt    GO    CREATE FUNCTION  lt inline function name  sysname  test function gt       lt  param1  sysname   p1 gt   lt data type for param1    int gt        lt  param2  sysname   p2 gt   lt data type for param2    char gt     RETURNS TABLE    AS   RETURN SELECT    p1 AS c1              p2 AS c2   GO    --                                                 -- Example to execute function   --                                                 SELECT      FROM  lt owner    dbo gt   lt inline function name  sysname  test function gt           lt value for  param1    1 gt            lt value for  param2     a  gt     GO

User · Answer

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

[tsql] Return multiple values from a SQL Server function

Examples related to tsql

Examples related to user-defined-functions