Using a SET statement inside of a CASE Statement

  • 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

     

  • 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;

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • 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

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • 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

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

  • 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

  • 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

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

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