End Case in a Variable
-
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
-
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
-
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 PLNever underestimate the power of human stupidity RAH