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

make it a table-valued function

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

Answer 2

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

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'')
DROP FUNCTION
GO

CREATE FUNCTION
(<@param1, sysname, @p1> ,
<@param2, sysname, @p2> )
RETURNS TABLE
AS
RETURN SELECT @p1 AS c1,
@p2 AS c2
GO

-- =============================================
-- Example to execute function
-- =============================================
SELECT *
FROM .
(,
)
GO