Parameter is null, return all?

Hi, heres the problem, ive got a table, and im calling a stored
procedure on the table. Now lets say a row contains the numbers 1
through 5, and i had a parameter in the sp that would be given one of
those values, and then return the corresponding row, but if they it
were passed a null value, how could i make it return all objects?Right
now i have this but i also want to return level 2, 3, 4, 5, etc:
SecurityID = ISNULL(@.SecurityLevel, '1')You can use a WHERE clause like
WHERE (
@.SecurityLevel = SecurityID
OR
@.SecurityLevel IS NULL
)
This will often perform poorly, because there is no one
query plan that can efficiently serve both cases, and another
thing to try is
WHERE (
SecurityID >= COALESCE(@.SecurityLevel,-2147483648)
AND
SecurityID <= COALESCE(@.SecurityLevel,2147483647)
)
(or with other values if the type of SecurityID is not INT).
Steve Kass
Drew University
nbs.tag@.gmail.com wrote:

>Hi, heres the problem, ive got a table, and im calling a stored
>procedure on the table. Now lets say a row contains the numbers 1
>through 5, and i had a parameter in the sp that would be given one of
>those values, and then return the corresponding row, but if they it
>were passed a null value, how could i make it return all objects?Right
>now i have this but i also want to return level 2, 3, 4, 5, etc:
>SecurityID = ISNULL(@.SecurityLevel, '1')
>
>|||SecurityID = @.SecurityLevel OR @.SecurityLevel IS NULL
Also see
http://sommarskog.se/dyn-search.html
--
Regards
Roji. P. Thomas
http://toponewithties.blogspot.com
<nbs.tag@.gmail.com> wrote in message
news:1148911602.878618.143780@.j55g2000cwa.googlegroups.com...
> Hi, heres the problem, ive got a table, and im calling a stored
> procedure on the table. Now lets say a row contains the numbers 1
> through 5, and i had a parameter in the sp that would be given one of
> those values, and then return the corresponding row, but if they it
> were passed a null value, how could i make it return all objects?Right
> now i have this but i also want to return level 2, 3, 4, 5, etc:
> SecurityID = ISNULL(@.SecurityLevel, '1')
>