Trying to get a case statement to work correctly

[dmorand]

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 ','.

DECLARE @location as char(1)
SET @location = 'B'
select * 
from smsdss.encv f
where cast(f.hpohierhpoobjid as varchar(25)) IN 
    (case when @location = 'B' then ('1004088425,1002573976,1003204285')
    else null
    end)
 

Any help would be appreciated.

[dmorand]

I tried to see if this would work, but no luck

DECLARE @location as char(1)
SET @location = 'B'
DECLARE @value as varchar(255)
SET @value = '''1004088425'',''1002573976'',''1003204285'''
select @value
select top 100 * 
from smsdss.encv f
where cast(f.hpohierhpoobjid as varchar(10)) IN 
    (case when @location = 'B' then (@value)
    end)
 

[ck9663]

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

[dmorand]

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

DECLARE @location as char(1)
SET @location = 'B'
select top 100 * 
from smsdss.encv f
where f.hpohierhpoobjid IN 
    (case when @location = 'B' then (1004088425,1002573976,1003204285)
    end)
 

[ck9663]

why not just include the @location check on the where clause

something like...

where f.hpohierhpoobjid IN  (1004088425,1002573976,1003204285)
 and  @location = 'B'
 

--- CK