use returned function value twice in query

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

SQL Server Development (2000)

use returned function value twice in query

Author

Topic

luciffer
Starting Member

8 Posts

Posted - 2004-07-07 : 23:22:31

i have a function f1 that returns an int, i need to pass f1 two params from teh current query, but i want to use the result as two different columns, how can i do this without calling the function twice?

what i've got now...(the paid column will end up being boolean thats why 1 or 0)

select draw_id, ticket_id,
dbo.f1(draw_id, ticket_id) as amount
convert(bit, (
case
when (dbo.f1(draw_id, ticket_id) > 0) then 1 else 0
end
)) as paid
from table_a

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-07-08 : 00:35:16

Rewrite the function to return a table. You'll join it to the table_a in the FROM clause on draw_id and ticket_id. That's more efficient then calling the function in a column anyway.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2004-07-08 : 01:36:09

select draw_id, ticket_id, amount,
convert(bit, (
case
when (amount > 0) then 1 else 0
end
)) as paid
from (
select *, dbo.f1(draw_id, ticket_id) as amount
from table_a
) as A

You may want to expand out the *

I don't understand what Derrick is suggesting: wouldn't that need a function that outputs rows for every possible draw_id and ticket_id, or require CROSS APPLY? (which, assuming you're not using a SQL Server 2005 beta, you don't have.)

luciffer
Starting Member

8 Posts

Posted - 2004-07-08 : 09:50:02

Thanks Arnold, your method makes sense to me. Derrick's did not because the function returns one value based on a single draw_id and ticket_id.

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2004-07-08 : 09:53:56

You could assign the result of the function to a variable and use the variable in the query, or the derived table in Arnold's reply.

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2004-07-08 : 10:59:32

I should probably have pointed out that it'll still generate a execution plan with two calls to dbo.f1 in it, even though you've only mentioned it once

But it was 06:30 when I posted (<expletive/> support call at 05:50).

luciffer
Starting Member

8 Posts

Posted - 2004-07-08 : 18:42:10

Is there a way to do it with only making one call to f1 in the execution plan?

I'm not sure i understand assigning the result to a variable, because f1 needs to be called for every row returned in the query, that's why the derived table looked good.

luciffer
Starting Member

8 Posts

Posted - 2004-07-08 : 18:47:10

PS - really new to this, i'm using MSDE with VS.NET 2003 anyone know how to get the execution plan for a SP in the VS.NET editor?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources