UDF

Question

UDF

Jeremy Kemp

SSCertifiable

Points: 6754
More actions
November 4, 2003 at 3:29 am

#83182
Can scaler UDF return multiple values?
I have a UDF which currently returns the name of a user (who owns an item) but I need to change this to return both their name and their user id.
I can do this by return a table from the UDF but it seems a bit of overkill to return 2 values.
I have tried returning two varchar fields but I keep getting a syntax error.
Current UDF looks like:
```
CREATE FUNCTION [dbo].[fn_Name] (@id int)  

RETURNS varchar(50) AS  



BEGIN 



   return ( select user_name from <table> where id = @id)



END 
```
What I have tried is this:
```
CREATE FUNCTION [dbo].[fn_Name] (@id int)  

RETURNS varchar(50), varchar(8) AS  



BEGIN 



   return ( select user_name, user_id from <table> where id = @id)



END 
```
but I get a syntax error.
Any ideas?
Thanks
Jeremy

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

paulhumphris Hall of Fame Points: 3496 More actions · Answer 1

You could try concatenating the two values together with say a pipe delimiter then returning them as a single varchar.

You would then have to split them up again in the calling procedure, but it would allow you to use the UDF to return two values.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 2

I think, you've already answered your own question.

From BOL:

quote:
Scalar functions
Operate on a single value and then return a single value. Scalar functions can be used wherever an expression is valid. This table categorizes the scalar functions.

Don't know, if this might help you

http://www.algonet.se/~sommar/arrays-in-sql.html

Frank

http://www.insidesql.de

http://www.familienzirkus.de

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Jeremy Kemp SSCertifiable Points: 6754 More actions · Answer 3

It's an idea but if I'm going to all that trouble I might as well return a table.

Jeremy

Frank Kalis SSC Guru Points: 111183 More actions · Answer 4

If you explain what you want to do with the result, we might find another work-around?

Frank

http://www.insidesql.de

http://www.familienzirkus.de

Edited by - Frank Kalis on 11/04/2003 03:44:15 AM

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Jeremy Kemp SSCertifiable Points: 6754 More actions · Answer 5

Frank,

I have a temporary table which is populated from several stored procedures/UDFs and is ultimately returned to the client app.

I currenly have a UDF which returns the owner's name (this information is inserted into the #table). As the item can only have one owner, the UDF currently returns a single varchar variable.

I want to change this UDF to return both the name of the owner and also their user id. As the item can only ever have one owner, I thought that returning a table from the UDF (which could hold many rows in a different situation) was excessive and I wanted the UDF to return two varchar values.

I could create a stored procedure with two output parameters to do the same but I wanted to know (curiosity really) whether a UDF can return two varchar fields (other than using a table variable).

Jeremy

Frank Kalis SSC Guru Points: 111183 More actions · Answer 6

In this case Paul's solution should be fine.

I thought you'd use the results (eg, the ID) for some further operations.

Frank

http://www.insidesql.de

http://www.familienzirkus.de

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Jeremy Kemp SSCertifiable Points: 6754 More actions · Answer 7

Frank,

I've only just spotted your quote from BOL which answers the question.

Thanks.

Jeremy

Satnam Grasshopper Points: 13 More actions · Answer 8

You could return the two values as a comma separated string and parse them in the calling procedure.