SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 UDF calling by reference? How to return 2 values?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

SQLIsTheDevil
Posting Yak Master

USA
177 Posts

Posted - 04/21/2008 :  10:38:46  Show Profile  Reply with Quote
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

Sweden
30111 Posts

Posted - 04/21/2008 :  10:57:11  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"

Edited by - SwePeso on 04/21/2008 10:58:32
Go to Top of Page

SQLIsTheDevil
Posting Yak Master

USA
177 Posts

Posted - 04/21/2008 :  11:08:53  Show Profile  Reply with Quote
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

Sweden
30111 Posts

Posted - 04/21/2008 :  11:16:26  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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

USA
177 Posts

Posted - 04/22/2008 :  10:21:23  Show Profile  Reply with Quote
thank you.
Go to Top of Page

blindman
Flowing Fount of Yak Knowledge

USA
2365 Posts

Posted - 04/22/2008 :  10:42:43  Show Profile  Reply with Quote
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

Sweden
30111 Posts

Posted - 04/22/2008 :  10:51:34  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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

Sweden
30111 Posts

Posted - 04/22/2008 :  10:56:10  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
2365 Posts

Posted - 04/22/2008 :  13:00:18  Show Profile  Reply with Quote
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

USA
7423 Posts

Posted - 04/22/2008 :  13:38:49  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
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

Sweden
30111 Posts

Posted - 04/22/2008 :  14:23:17  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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

USA
177 Posts

Posted - 04/22/2008 :  16:08:12  Show Profile  Reply with Quote
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

USA
7423 Posts

Posted - 04/22/2008 :  17:30:16  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
2365 Posts

Posted - 04/22/2008 :  19:55:18  Show Profile  Reply with Quote
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

USA
177 Posts

Posted - 04/23/2008 :  08:50:50  Show Profile  Reply with Quote
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.

Edited by - SQLIsTheDevil on 04/23/2008 08:52:43
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000