optional parameter in sql query

[parez]

Hi All,

I have a stored proc which looks like this.

Create ....
(
@id int,
@ud int,
@td int=0
)

if @td=0
select bkah from asdf where id=@id and ud=@ud
else
select bkah from asdf where id=@id and ud=@ud and td=@td

---------------------------------
I am wondering if i could replace if condition with the following line

select bkah from asdf where id=@id and ud=@ud
and ( @td<>0 and td>@td )

IS sql server 2000 smart enough not to use the td>@td in the query if
@td is 0
Thanks all

[Erland Sommarskog]

parez (ps*****@gmail.com) writes:

I have a stored proc which looks like this.

Create ....
(
@id int,
@ud int,
@td int=0
)

if @td=0
select bkah from asdf where id=@id and ud=@ud
else
select bkah from asdf where id=@id and ud=@ud and td=@td

---------------------------------
I am wondering if i could replace if condition with the following line

select bkah from asdf where id=@id and ud=@ud
and ( @td<>0 and td>@td )

IS sql server 2000 smart enough not to use the td>@td in the query if
@td is 0

This looks a little strange. If you pass @td = 0, the latter query
will not return any rows. Possibly you mean:

select bkah from asdf where id=@id and ud=@ud and
(@td = 0 OR @td = td)

This works, but keep in mind that when SQL Server builds the query
plan. it does so without knowing of the actual value of @td at
time time of the statement. Thus if there is an index on td that
you want to be used when @td is non-zero, you should not do the above.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[parez]

Hi

I want to use the td column in the query only if @td variable is
passed to the stored procedure.

Thannks

[Erland Sommarskog]

parez (ps*****@gmail.com) writes:

I want to use the td column in the query only if @td variable is
passed to the stored procedure.

Then the original pair of queries you had, will work fine.

For a much longer discussion of the more general problem when you have
many of these @td parameters, there is an article on my web site:
http://www.sommarskog.se/dyn-search.html.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp