Issue with CASE statement within WHERE clause

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 2008 Forums

Transact-SQL (2008)

Issue with CASE statement within WHERE clause

Author

Topic

Goalie35
Yak Posting Veteran

81 Posts

Posted - 2011-07-19 : 11:20:12

I have a select statement that takes a BIT parameter to determine whether or not to include California orders. If the parameter passes in false, I want to include some additional text in my where clause. Unfortunately though, I'm getting an error with my CASE statement:
DECLARE @includeCalifornia BIT
SET @includeCalifornia = 0
EXEC
('
SELECT
FirstName,
LastName
FROM
dbo.Orders
WHERE
OrderDate = GETDATE()
AND CASE WHEN @includeCalifornia = 0 THEN (OrderState <> ''''CA'''') END
'')
')

-----
Any idea what's wrong with my case statement?

Thanks.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-07-19 : 11:51:19

No need for dynamic SQL

SELECT	FirstName,
	LastName
FROM	dbo.Orders
WHERE	OrderDate = GETDATE()
AND	(
	OrderState <> 'CA'
	OR
	@includeCalifornia = 1
)

Note that getdate() will include time info, so you may not get any results unless you strip off the time portion

Goalie35
Yak Posting Veteran

81 Posts

Posted - 2011-07-19 : 13:37:34

Hi Russell, thanks for the update.

I agree with you there's no need for dynamic sql here but I'd prefer to leave it in. It's actually only my 2nd day on a new job at a place that seems to write most of their queries in this fashion. Not a fan of their queries, but for now, I'll go ahead and follow their format.

So I wrote your suggestion as follows but received an error:
----------------------------------------
DECLARE @includeCalifornia BIT
SET @includeCalifornia = 0
EXEC
('
SELECT FirstName,
LastName
FROM dbo.Orders
WHERE OrderDate = GETDATE()
AND (
OrderState <> ''''CA''''
OR
@includeCalifornia = 1
)
'')
')
----------------------------------------
Thanks again.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-07-19 : 13:43:46

You might want to investigate sp_executesql to make your dynamic sql safer as well as gaining he ability to handle parameters easier. However, if you wanted to stick with the same methodology you need to convert your variabled to strings and concatenate them:

DECLARE @includeCalifornia BIT
SET @includeCalifornia = 0
EXEC
('
SELECT FirstName,
LastName
FROM dbo.Orders
WHERE OrderDate = GETDATE()
AND (
OrderState <> ''''CA''''
OR
' + CAST(@includeCalifornia AS CHAR(1)) + ' = 1
)
'')
')

Also, here is a link that has a lot of information about dynamic SQL that might be helpful:
http://www.sommarskog.se/dynamic_sql.html

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-07-19 : 13:45:50

^ What Lamprey said.

But, if you're writing a new procedure, better to do it right than to follow their format.

Small fix to Lamprey's code:

DECLARE @includeCalifornia BIT
SET @includeCalifornia = 0
exec
('
SELECT FirstName,
LastName
FROM dbo.Orders
WHERE OrderDate = GETDATE()
AND (
OrderState <> ''CA''
OR
' + CAST(@includeCalifornia AS CHAR(1)) + ' = 1
)
')

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-07-19 : 13:47:40

Also, it can be hard to tell how many single quotes you need, so you might also want to change them to CHAR(39)s.. for example:

DECLARE @includeCalifornia BIT
SET @includeCalifornia = 0
EXEC
('
SELECT FirstName,
LastName
FROM dbo.Orders
WHERE OrderDate = GETDATE()
AND (
OrderState <> ' + CHAR(39) + 'CA' + CHAR(39) + '
OR
' + CAST(@includeCalifornia AS CHAR(1)) + ' = 1
)
'')
')

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-07-19 : 13:50:39

Exactly. I'll change the EXEC to PRINT so I can see what it's doing.

Goalie35
Yak Posting Veteran

81 Posts

Posted - 2011-07-20 : 14:34:15

Great, it's working now. Thanks to both of you.

Yeah, it was based on a pre-existing SP, so I didn't want to go and start modifying their code...at least not on the first few days of the job ;)

Thanks again

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-07-20 : 14:53:03

I love telling 'em how smart I am and how stupid they are my first week on the job lol. Just kidding...kinda...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources