Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 UDF calling by reference? How to return 2 values?

Author  Topic 

SQLIsTheDevil
Posting Yak Master

177 Posts

Posted - 2008-04-21 : 10:38:46
I'm having a little trouble with a UDF I'm creating. It doesn't appear I can pass parameters by reference; the UDF is in its own scope. Is this the case?

The other issue I'm having is I'd like to return two values from the function. How do I go about this?

Thank you.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-21 : 10:57:11
Use SQL Server 2005 and CROSS APPLY.

If you want to include the two values as two columns in the resultset, you must use two functions.
Or concatenate them as one string and then split the string in the resultset.

To use column values as function parameter you can only return a scalar value in SQL Server 2000.



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

SQLIsTheDevil
Posting Yak Master

177 Posts

Posted - 2008-04-21 : 11:08:53
Thanks. What about table functions, would that be of any use? I'm not performing any queries within the function itself, so I don't know if a table function is appropriate.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-21 : 11:16:26
quote:
Originally posted by Peso

To use column values as function parameter you can only return a scalar value in SQL Server 2000.

To use column values as function parameter you can only return a scalar value [from a function] in SQL Server 2000.


E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

SQLIsTheDevil
Posting Yak Master

177 Posts

Posted - 2008-04-22 : 10:21:23
thank you.
Go to Top of Page

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2008-04-22 : 10:42:43
Huh? Are you saying the poster can't return her two values as a table with two columns and a single row?

e4 d5 xd5 Nf6
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-22 : 10:51:34
Yes.

This will not work in either SQL Server 2000 nor SQL Server 2005
CREATE FUNCTION dbo.fnTest1
(
@Value INT
)
RETURNS TABLE
AS
RETURN (
SELECT @Value * 2 AS t2,
@Value * 4 AS t4
)
GO
CREATE FUNCTION dbo.fnTest2
(
@Value INT
)
RETURNS TABLE
AS
RETURN (
SELECT @Value * 2 AS t

UNION ALL

SELECT @Value * 4
)
GO

SELECT Number,
dbo.fnTest1(Number)
FROM master..spt_values
WHERE Type = 'p'
AND Number < 5

SELECT Number,
dbo.fnTest2(Number)
FROM master..spt_values
WHERE Type = 'p'
AND Number < 5
But this will work in SQL Server 2005
SELECT		Number,
t2,
t4
FROM master..spt_values
CROSS APPLY dbo.fnTest1(Number)
WHERE Type = 'p'
AND Number < 5

SELECT Number,
t
FROM master..spt_values
CROSS APPLY dbo.fnTest2(Number)
WHERE Type = 'p'
AND Number < 5



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-22 : 10:56:10
With the sample code above, you can see that a call to the function will work but not within as a part of a resultset.



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2008-04-22 : 13:00:18
But she never said she wanted it as part of a result set. I'm not sure where you inferred that she wants to CROSS APPLY.

She simply said she wants to get two values from the function.


declare @Result1 int
declare @Result2 int

select @Result1 = t2,
@Result2 = t4
from dbo.fnTest1(4)

select @Result1 as Result1, @Result2 as Result2


e4 d5 xd5 Nf6
Go to Top of Page

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-04-22 : 13:38:49
I have a simple article on this:

http://www.sqlteam.com/article/returning-complex-data-from-user-defined-functions-with-cross-apply

Blindman -- CROSS APPLY would be used if the OP wants to return 2 pieces of data for each row in a table or result set. If they only want to return 2 single values without any joins or relations to other data, then, yes, they would not need CROSS APPLY.

- Jeff
http://weblogs.sqlteam.com/JeffS
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-22 : 14:23:17
quote:
Originally posted by blindman

But she never said she wanted it as part of a result set. I'm not sure where you inferred that she wants to CROSS APPLY.

She simply said she wants to get two values from the function.

If that's the case, I think a stored procedure with output parameters maybe would be easier to use?



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

SQLIsTheDevil
Posting Yak Master

177 Posts

Posted - 2008-04-22 : 16:08:12
As I understand it, functions and procedures are modules. And as such, they are call-by-value routines innately. This presented some issues and what I ended up taking the advice of returning the value as a delimited string. Then, I split it to two variables and processed them accordingly.
Go to Top of Page

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-04-22 : 17:30:16
quote:
Originally posted by SQLIsTheDevil

As I understand it, functions and procedures are modules. And as such, they are call-by-value routines innately. This presented some issues and what I ended up taking the advice of returning the value as a delimited string. Then, I split it to two variables and processed them accordingly.



You should instead consider reading the article link I posted and also looking at Peso's suggestions.

- Jeff
http://weblogs.sqlteam.com/JeffS
Go to Top of Page

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2008-04-22 : 19:55:18
Yeah, you'd have to give us more details about exactly what you are doing before we could give you the optimal solution, but I have to say I doubt it would be the option you have chosen.

e4 d5 xd5 Nf6
Go to Top of Page

SQLIsTheDevil
Posting Yak Master

177 Posts

Posted - 2008-04-23 : 08:50:50
quote:
Peso Wrote
Use SQL Server 2005 and CROSS APPLY.

If you want to include the two values as two columns in the resultset, you must use two functions.
Or concatenate them as one string and then split the string in the resultset.

To use column values as function parameter you can only return a scalar value in SQL Server 2000.



I did take Peso's advice. Anyway, I'll keep the article as a favorite. Thank you.
Go to Top of Page
   

- Advertisement -