|
|
Posted by Erland Sommarskog on 11/07/05 01:21
parez (psawant@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, esquel@sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
Navigation:
[Reply to this message]
|