Using a SET statement inside of a CASE Statement

Question

Using a SET statement inside of a CASE Statement

RedSoxRay

SSC Enthusiast

Points: 194
More actions
February 19, 2021 at 5:48 pm

#3847427

I want to update the contents of a variable using a CASE Statement; for example:
I declare the variable and set the initial value, then I want to use a CASE Statement to append to that variable contents. So, in my example below, it initially sets the variable to 'The Following Items Have Changed:' . Using the CASE Statement, if it is true, the variable would be appended to be 'The Following Items Have Changed: some more stuff'.
I can't seem to get this to work. Is there any way to do what I want?
Thanks!

DECLARE @TheDetails VARCHAR(MAX)
SET @TheDetails = 'The Following Items Have Changed: '
SELECT
CASE WHEN ValueA <> ValueB THEN SET @TheDetails += 'Some more stuff'
ELSE ''
END

Viewing 7 posts - 1 through 6 (of 6 total)

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

Phil Parkin SSC Guru Points: 246988 More actions · Answer 1

This syntax works ... not sure whether it gets you where you want to be.

DECLARE @TheDetails VARCHAR(MAX);

SET @TheDetails = 'The Following Items Have Changed: ';

IF 1 <> 0
    SET @TheDetails += 'Some more stuff';

SELECT @TheDetails;

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

ScottPletcher SSC Guru Points: 101126 More actions · Answer 2

In SQL Server, the CASE WHEN/THEN results are scalar (single) values only: keywords, operators, etc. are not allowed. Thus, you need something more like below.

Edit: That said, the expressions within a CASE can be quite complex, and even include subqueries, but ultimately the result must come down to a single value.

DECLARE @TheDetails varchar(MAX)

SET @TheDetails = 'The Following Items Have Changed: '

SELECT
    @TheDetails += CASE WHEN ValueA <> ValueB THEN 'Some more stuff' ELSE '' END

This reply was modified 3 years, 10 months ago by ScottPletcher.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Eddie Wuerch SSChampion Points: 12535 More actions · Answer 3

Another key point is that you don't use SET inside a SELECT statement to assign a value to a variable, you just assign a value:

DECLARE @MyVal varchar(64);
SELECT @MyVal = 'Assigned without SET';
SELECT @MyVal AS [MyVal];

Your statement would work by removing the SET and using the CASE to produce a value to add to @TheDetails:

DECLARE @TheDetails VARCHAR(MAX);

SELECT @TheDetails = 'The Following Items Have Changed: ';

SELECT @TheDetails += CASE WHEN ValueA != ValueB THEN 'Some more stuff'
                           ELSE ''
                      END;

Eddie Wuerch
MCM: SQL

RedSoxRay SSC Enthusiast Points: 194 More actions · Answer 4

Thanks so much for all of your responses; very much appreciated!

Kristen-173977 SSCrazy Eights Points: 8687 More actions · Answer 5

Can you do it in one statement?

SELECT@TheDetails = CONCAT('The Following Items Have Changed:'
, CASE WHEN ValueA <> ValueB THEN ' Some more stuff' ELSE '' END
, CASE WHEN ValueC <> ValueD THEN ' Even more stuff' ELSE '' END
)

You'll get NULL if there are no ROWS included for the ValueA/ValueB part ... so possibly this as an alternative

SELECT@TheDetails = 'The Following Items Have Changed:'
SELECT@TheDetails = CONCAT(@TheDetails
, CASE WHEN ValueA <> ValueB THEN ' Some more stuff' ELSE '' END
, CASE WHEN ValueC <> ValueD THEN ' Even more stuff' ELSE '' END
)
FROM ...

but may not be relevant in your case.

CONCAT will convert any NULL values to EmptyString, whereas

SELECT @TheDetails += NULL

will set @TheDetails to NULL

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 6

Conceptually, you must always remember that CASE is an expression, not a statement. You use it just as you would use a function, or a formula, to deliver a single value. There is no need for a SELECT, you just SET your variable to a value returned by the case expression.

declare @A int = 2 
  , @B int = 1
  , @details varchar(100) = 'Some stuff'

set @details = case when @A > @B then @details + ' and some more stuff.'
else @details
end

select @details

-- alternative 

set @details = @details + case when @A > @B then ' and some more stuff.'
else ''
end

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills