By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,767 Members | 1,302 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,767 IT Pros & Developers. It's quick & easy.

Trying to get a case statement to work correctly

100+
P: 219
I'm trying to test some logic I want to include in a SQL query I have.
I want to be able to check against a group of values when a user sets a parameter value (location). I tried the query below but get no results, and I know why. The number values I have below need to have quotes around each number, but I can't do that because then the sql statement becomes invalid and I get an error:

Server: Msg 170, Level 15, State 1, Line 6
Line 6: Incorrect syntax near ','.

Expand|Select|Wrap|Line Numbers
  1. DECLARE @location as char(1)
  2. SET @location = 'B'
  3. select * 
  4. from smsdss.encv f
  5. where cast(f.hpohierhpoobjid as varchar(25)) IN 
  6.     (case when @location = 'B' then ('1004088425,1002573976,1003204285')
  7.     else null
  8.     end)
  9.  
Any help would be appreciated.
May 12 '09 #1
Share this Question
Share on Google+
4 Replies


100+
P: 219
I tried to see if this would work, but no luck

Expand|Select|Wrap|Line Numbers
  1. DECLARE @location as char(1)
  2. SET @location = 'B'
  3. DECLARE @value as varchar(255)
  4. SET @value = '''1004088425'',''1002573976'',''1003204285'''
  5. select @value
  6. select top 100 * 
  7. from smsdss.encv f
  8. where cast(f.hpohierhpoobjid as varchar(10)) IN 
  9.     (case when @location = 'B' then (@value)
  10.     end)
  11.  
May 12 '09 #2

ck9663
Expert 2.5K+
P: 2,878
You need to put a quote on each...

case when @location = 'B' then ('1004088425','1002573976','1003204285')

or not convert your f.hpohierhpoobjid and just use

case when @location = 'B' then (1004088425,1002573976,1003204285)

your NULL however might have a problem....

read this

Good luck


--- CK
May 12 '09 #3

100+
P: 219
I tried this but get an error (Line 6: Incorrect syntax near ','.) :

Expand|Select|Wrap|Line Numbers
  1. DECLARE @location as char(1)
  2. SET @location = 'B'
  3. select top 100 * 
  4. from smsdss.encv f
  5. where f.hpohierhpoobjid IN 
  6.     (case when @location = 'B' then (1004088425,1002573976,1003204285)
  7.     end)
  8.  
May 12 '09 #4

ck9663
Expert 2.5K+
P: 2,878
why not just include the @location check on the where clause

something like...

Expand|Select|Wrap|Line Numbers
  1. where f.hpohierhpoobjid IN  (1004088425,1002573976,1003204285)
  2.  and  @location = 'B'
  3.  
--- CK
May 13 '09 #5

Post your reply

Sign in to post your reply or Sign up for a free account.