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]

Re: optional parameter in sql query

parez (psawant@gmail. com) writes:[color=blue]
> 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[/color]

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, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[parez]

Re: optional parameter in sql query

Hi

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

Thannks

[Erland Sommarskog]

Re: optional parameter in sql query

parez (psawant@gmail. com) writes:[color=blue]
> I want to use the td column in the query only if @td variable is
> passed to the stored procedure.[/color]

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, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp