Building where clauses conditionally in a sp

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)

Building where clauses conditionally in a sp

Author

Topic

michhes
Starting Member

2 Posts

Posted - 2009-01-01 : 21:21:11

Hi all,

I'm trying to conditionally apply WHERE clauses to a query stored in a transact-sql stored procedure but SQL Server 2005 doesn't much care for this syntax:


select *
from product
where
 if @searchtype = 'name'
  product_name = @name
 else if @searchtype = 'id'
  product_id = @id

I've also looked at CASE/WHEN but I don't believe that mechanism would work in this example.

Basically I want to end up with a single, parameterized stored procedure that can conditionally apply WHERE filters to avoid having multiple sprocs with minor differences and duplicate code in a top-level if block.

Any ideas? My next thought was to run the base query and then progressively filter its results but that seems unnecessarily heavy.

TIA,
M

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-01-01 : 22:00:57

SELECT *
FROM product
WHERE (@searchtype = 'name' AND product_name = @name) OR (@searchtype = 'id' AND product_id = @id)

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-02 : 01:21:17

[code]SELECT *
FROM product
WHERE (@searchtype <> 'name' OR product_name = @name)
AND(@searchtype <> 'id' OR product_id = @id)
[/code]

michhes
Starting Member

2 Posts

Posted - 2009-01-02 : 01:39:36

quote:
Originally posted by tkizer

SELECT *
FROM product
WHERE (@searchtype = 'name' AND product_name = @name) OR (@searchtype = 'id' AND product_id = @id)

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Brilliant Tara!!! That worked perfectly! Any thoughts how I might extend this to the order by clause as well?

Pseudo code here:

SELECT *
FROM product
WHERE (@searchtype = 'name' AND product_name = @name)
OR (@searchtype = 'id' AND product_id = @id)
--order by product_name if @searchtype = 'name')
--order by product_id if @searchtype = 'id'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-02 : 01:49:06

[code]SELECT *
FROM product
WHERE (@searchtype = 'name' AND product_name = @name)
OR (@searchtype = 'id' AND product_id = @id)
order by case when @searchtype = 'name' then product_name else 1 end,
case when @searchtype = 'id' then product_id else 1 end
[/code]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-01-02 : 02:01:59

For more informations read
www.sommarskog.se/dyn-search.html

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources