SQL Server 2000 "Incorrect syntax near the keyword 'CASE'"

Question

The error is return when the following stored proc is attempted to be executed:

EXECUTE p_someProc
--list of vars
CASE WHEN @var1=1 AND @var2=1 THEN 3 
     WHEN @var2=1 THEN 2 
     WHEN @var1=1 THEN 1 END,
--more vars

There are other CASE functions included, though only a single error is spewed up which points to the first CASE.

When the identical expression is run within a PRINT function, an expected result is returned.

declare @var1 bit set @var1 = 1
declare @var2 bit set @var2 = 1

print(CASE WHEN @var1=1 AND @var2=1 THEN 3 
     WHEN @var1=1 THEN 2 
     WHEN @var2=1 THEN 1 END)

The output is '3'

I assume that I'm misusing the CASE function somehow. Could anyone think of a workaround or a way to fix it?

Thanks in advance.

--Stan

Answer 1

Hard to tell without more code. Check that @var1 and @var2 have been declared, perhaps?

Make sure you're assigning the result of the CASE to something, too!

Answer 2

As JeremyMcGee alludes to above you need to perform an assignment rather than trying to pass the case to the exec, try something more like the following

Declare @varx int
select @varx = CASE WHEN @var1=1 AND @var2=1 THEN 3 
     WHEN @var2=1 THEN 2 
     WHEN @var1=1 THEN 1 END
EXECUTE p_someProc @varx