|
I have a case statement. I want to save the case value in a variable so that I can use the variable later in the stored procedure.
Ex:
case when Flag = 'Y' then CONVERT(varchar(10), (LastDate), 120) end as @var1,
case when @var1 = ... then ..... end
Please help
|
|
|
|
|
SET @VariableName = CASE Flag When 'Y' then etc...
or
select @VariableName = CASE Flag When 'Y' then etc...
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
the Stored proc is as follows:
select empno, datejoined,
case when Flag = 'Y' then CONVERT(varchar(10), (LastDate), 120) end as @var1,
case when @var1 = ... then ..... end
from tableemp
|
|
|
|
|
You are combining a select query and an assignment query, not allowed!
You need to nest your case statements as you need to test the case for each record, variable assignment is a once only operation (using a cursor spit would allow you to do the record by record processing).
This example test the value of flag but note that it must know what to expect in the nested case, your ... cannot be a random date, it must be a testable value!
SELECT Records,
CASE Flag WHEN 'FX' THEN CASE Records WHEN 0 THEN 'Failed' ELSE 'Ok' END
WHEN 'MM' THEN CASE Records WHEN 0 THEN 'Failed' ELSE 'Ok' END
WHEN 'TZ' THEN CASE Records WHEN 0 THEN 'Failed' ELSE 'Ok' END END
FROM ProcessLog PL
Never underestimate the power of human stupidity
RAH
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
