Exclude param from where if 0 - 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 2000 Forums

Transact-SQL (2000)

Exclude param from where if 0

Author

Topic

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2007-03-01 : 10:07:04

I need to improve this stored procedure and get the if statement out.

Bottom line, I want to exclude the last AND if the parameter @location > 0

<snip>
WHERE a.manufacturer = b.manufacturerID
AND a.location = c.locationID
AND a.Status = d.statusID
AND a.EquipmentType = e.ID
AND a.calLab = f.ID
AND c.locationID = @location

So, if the INT @location > 0 then don't include the last AND line in the where clause.

Thanks,

Zath

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-03-01 : 10:23:04

[code]<snip>
WHERE a.manufacturer = b.manufacturerID
AND a.location = c.locationID
AND a.Status = d.statusID
AND a.EquipmentType = e.ID
AND a.calLab = f.ID
AND (c.locationID = @location OR @location > 0)[/code]

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2007-03-01 : 10:59:43

Thanks!

Worked great!

Zath

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources