How do I use OUTPUT to return more than one variable from a function in SQL Server2005

I know I could开发者_C百科 create a table-valued function if I want to return more than one variable from a function but for function where I would always be returning only one record, it seems as an overkill. I would prefer to return all the values as variables instead. I guess this could be done using OUTPUT clause. Would appreciate if you let me know that its possible and if it is, guide me how to go about it? Didn't find a straight forward way in msdn for this.

Thanks.

---

You need OUTPUT parameters rather than the OUTPUT clause if I understand your requirement correctly. (These only apply to stored procedures however)

Example

CREATE PROC outputdemo
@name varchar(50),
@status int OUTPUT,
@type nchar(3) OUTPUT
AS
SELECT @status = status, @type = type
FROM master..spt_values
WHERE name=@name /*<--Pretend this is the PK*/

GO

DECLARE @status int, @type nchar(3) 
EXEC outputdemo 'rpc', @status OUTPUT, @type OUTPUT
SELECT @status AS status, @type as type

If you are asking explicitly about scalar UDFs then the only way I can think of returning more than one value would be via XML, or a custom UDT or some other similar mechanism.

---

Unfortunately you cannot have output parameters for functions; you can only return a single scalar or a single table.

You can however have output parameters for procedures.

---

create procedure GetMultipleValues
(
    @ValueOne int out,          -- decorate parameters with out
    @ValueTwo nvarchar(100) out
)
as
set @ValueOne = 1
set @ValueTwo = N'Hello, world!'
return
go



declare @MyInteger int
declare @MyString nvarchar(100)

exec GetMultipleValues @MyInteger out, @MyString out  -- decorate arguments with out

print @MyInteger -- 1
print @MyString  -- Hello, world!