Prev: Convert datetime field to interval
Next: Number of subordinates using NESTED SET
From: tshad on 30 Mar 2010 20:36
I have a function that has 4 unions.

The queries are similar except for the where clauses

It looks something like:

SELECT...
FROM...
JOIN...
WHERE @Arg1 = 0 and @Arg2 = 0 and e.EID IS NULL
UNION
SELECT...
FROM...
JOIN...
WHERE @Arg1 = 0 and @Arg2 = 1
UNION
SELECT...
FROM...
JOIN...
WHERE @Arg1 = 1 and @Arg2 = 0 and e.EID IS NULL
UNION
SELECT...
FROM...
JOIN...
WHERE @Arg1 =1 and @Arg2 = 1

@Arg1 and @Arg2 are passed in and are 0 or 1.

Is this the best way to handle the possible combinations or would it be
better to do an if test and only do the one that matches what was passed it.

I wasn't sure if the Optimizer might do that anyway.

Thanks,

Tom


From: Plamen Ratchev on 30 Mar 2010 21:06
See Erland's article on the topic:
http://www.sommarskog.se/dyn-search.html

--
Plamen Ratchev
http://www.SQLStudio.com
From: tshad on 30 Mar 2010 21:30
It's a good article but not sure if it really answered my question.

After playing around with it, I think I'll just leave it as it is.

The table function would have to be changed to specifically define the table
(which it doesn't do now) and just returns what is in the select statement -
which you can't do if you want to have if statements.

Not sure it would be better anyway.

I assume the optimizer is smart enough to see the WHERE clauses and will
only pick the one it needs - since only one will ever be executed when the
function is called.

Thanks,

Tom

"Plamen Ratchev" <Plamen(a)SQLStudio.com> wrote in message
news:pNadneCLHc-vPS_WnZ2dnUVZ_hCdnZ2d(a)speakeasy.net...
> See Erland's article on the topic:
> http://www.sommarskog.se/dyn-search.html
>
> --
> Plamen Ratchev
> http://www.SQLStudio.com


 | 
Pages: 1
Prev: Convert datetime field to interval
Next: Number of subordinates using NESTED SET