Using CASE in 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

General SQL Server Forums

New to SQL Server Programming

Using CASE in where clause

Author

Topic

ranvir_2k
Posting Yak Master

180 Posts

Posted - 2014-04-30 : 10:49:01

I have the following query which is using a lot of duplicate code. I was wondering if there is a more efficient way to write this query.


delcare @status varchar (10)


	if @status = 'A'
        begin
 			select	*
			 from	my table 
 			where CODE = 12345
         end
	

	if @status = 'O'
        begin
			select	*
			from	my table 
			where CODE = 12345 and Name = ''
        end

Is there any way to put this into one query. As you can see when @status = 'O' the where clause of the query is different than if @status = 'A'.

Can you put a CASE in the where clause so that if @status is 'A' perform a different action than if it is 'O'.

Thanks

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-04-30 : 11:23:13

I usually write the query like this:

SELECT
	*
FROM [my table]
WHERE
	(@status = 'A' AND CODE = 12345)
	OR
	(@status = 'O' AND CODE = 12345 AND Name = '');

One potential problem with this approach is that if you are coding this into a stored procedure, and passing @status as a parameter, it can generate inefficient query plans for some values of the parameters. This blog explains the issue and solutions: http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2014-04-30 : 11:25:21

[code]select *
from MyTable
where
CODE = 12345
and (
@status = 'A'
OR
(
@status = 'O' AND Name = ''
)[/code]

===============================================================================
“Everyone wants a better life: very few of us want to be better people.”
-Alain de Botton

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources