Dynamic where clause - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Dynamic where clause

Author

Topic

John Sourcer
Yak Posting Veteran

91 Posts

Posted - 2008-10-25 : 06:07:27

Somehow this doesn't work where I have

SectorID
1
2
3

And I want to pass the query @sector = 0 for all results or @sector = 1 etc for just that result.

WHERE (SectorID = @sector OR SectorID != @sector)

Something stupid I missing?

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2008-10-25 : 06:37:34

try like this


WHERE (@sector = 0 OR SectorID = @sector)
if u pass @sectorid NULL also then
WHERE (@sector = 0 OR @sector IS NULL OR SectorID = @sector)

"There is only one difference between a dream and an aim. A dream requires soundless sleep to see, whereas an aim requires sleepless efforts to achieve..!!"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-25 : 07:08:33

or just use

WHERE SectorID=COALESCE(NULLIF(@Sector,0),SectorID)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources