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" |
|
|
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. |
|
|
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" |
|
|
SQLIsTheDevil
Posting Yak Master
177 Posts |
Posted - 2008-04-22 : 10:21:23
|
thank you. |
|
|
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 |
|
|
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 2005CREATE FUNCTION dbo.fnTest1( @Value INT)RETURNS TABLEASRETURN ( SELECT @Value * 2 AS t2, @Value * 4 AS t4 )GOCREATE FUNCTION dbo.fnTest2( @Value INT)RETURNS TABLEASRETURN ( SELECT @Value * 2 AS t UNION ALL SELECT @Value * 4 )GOSELECT Number, dbo.fnTest1(Number)FROM master..spt_valuesWHERE Type = 'p' AND Number < 5SELECT Number, dbo.fnTest2(Number)FROM master..spt_valuesWHERE Type = 'p' AND Number < 5 But this will work in SQL Server 2005SELECT Number, t2, t4FROM master..spt_valuesCROSS APPLY dbo.fnTest1(Number)WHERE Type = 'p' AND Number < 5SELECT Number, tFROM master..spt_valuesCROSS APPLY dbo.fnTest2(Number)WHERE Type = 'p' AND Number < 5 E 12°55'05.25"N 56°04'39.16" |
|
|
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" |
|
|
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 intdeclare @Result2 intselect @Result1 = t2, @Result2 = t4from dbo.fnTest1(4)select @Result1 as Result1, @Result2 as Result2 e4 d5 xd5 Nf6 |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
|
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" |
|
|
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. |
|
|
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.- Jeffhttp://weblogs.sqlteam.com/JeffS |
|
|
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 |
|
|
SQLIsTheDevil
Posting Yak Master
177 Posts |
Posted - 2008-04-23 : 08:50:50
|
quote: Peso WroteUse 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. |
|
|
|