By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,461 Members | 1,135 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,461 IT Pros & Developers. It's quick & easy.

optional parameter in sql query

P: n/a
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

Nov 6 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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

Nov 6 '05 #2

P: n/a
Hi

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

Thannks

Nov 7 '05 #3

P: n/a
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

Nov 7 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.