TSQL LAB 2 - Writing Conditional WHERE Clauses

Question

Viewing 2 posts - 106 through 106 (of 106 total)

You must be logged in to reply to this topic. Login to reply

chisholmd SSCrazy Points: 2490 More actions · Answer 1

declare @myParam int

set @myParam = 6 (or null)

select *

from entry

where

(entry.uid = @myParam OR @myParam is null)

No need to make the param zero. This worked as expected so that

when myparam = 6 it returned the single row

when myparam = null it returned all

when myparam = 666666666 it returned none. (an int not in the column)

On the face of it it looks more performant then the coalesce method. If I try some tests I will post the results.

Dave

Trainmark.com IT Training B2B Marketplace
(Jobs for IT Instructors)

luo_yingmin Grasshopper Points: 22 More actions · Answer 2

Based on my experience, the one using Dynamic SQL is the best solution. using or operator is not recommended,

1.cause it will SQL Server to misuse index.

2.When the traffic is high, it consume more CPU resource....