in clause in case statement

Question

in clause in case statement

Niteen.gavhane

SSC Rookie

Points: 38
More actions
January 14, 2011 at 2:56 am

#243660

i'm getting syntax error in following procedure
CREATE PROCEDURE testbizclose_remove
(
@Par int
)
AS
SELECT DAY FROM BIZ_CLOSING WHERE BIZ_ID = 802 and (case @Par when 1 then shift in( 1,4,6,7) when 2 then shift in(2,4,5,7) when 3 then shift (3,5,6,7) end )
need help

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259135 More actions · Answer 1

Niteen.gavhane (1/14/2011)
i'm getting syntax error in following procedure
CREATE PROCEDURE testbizclose_remove
(
@Par int
)
AS
SELECT DAY FROM BIZ_CLOSING WHERE BIZ_ID = 802 and (case @Par when 1 then shift in( 1,4,6,7) when 2 then shift in(2,4,5,7) when 3 then shift (3,5,6,7) end )
need help

What is the purpose of shift in( 1,4,6,7), shift in(2,4,5,7) and shift (3,5,6,7)?

What logic are you trying to create?

I believe you'll have to create a dynamic SQL statement. First you check the value of @Par and store the result in a string.

Then you construct the final select statement by concatenating the string SELECT DAY FROM BIZ_CLOSING WHERE BIZ_ID = 802 and with the other string.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

irobertson Hall of Fame Points: 3695 More actions · Answer 2

Hi,

Another way is to use a table variable to limit your values:

declare @tb table (Vals int)

if @Par = 1

begin

insert @tb values (1),(2)...

etc

SELECT DAY FROM BIZ_CLOSING WHERE BIZ_ID = 802 and Shift in (

select Vals from @tb

)

Just because I find it a bit easier to read than dynamic SQL...

Cheers, Iain

Edit: posted before complete!

Koen Verbeeck SSC Guru Points: 259135 More actions · Answer 3

irobertson (1/14/2011)
Hi,
Another way is to use a table variable to limit your values:
...
Just because I find it a bit easier to read than dynamic SQL...

You are right, that is a better readable solution.

I'll try to remember this one in case I ever need it.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP