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

Expand|Select|Wrap|Line Numbers

 
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

Expand|Select|Wrap|Line Numbers

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

Expand|Select|Wrap|Line Numbers

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

Expand|Select|Wrap|Line Numbers

 
where f.hpohierhpoobjid IN  (1004088425,1002573976,1003204285)

 and  @location = 'B'

--- CK

Trying to get a case statement to work correctly

re: Trying to get a case statement to work correctly

re: Trying to get a case statement to work correctly

re: Trying to get a case statement to work correctly

re: Trying to get a case statement to work correctly

Similar Microsoft SQL Server bytes

/bytes/development

/bytes/it

/bytes/about