T-SQL Case statement with 2 column values SQL Server

Question

I am trying to use a Case statement to check if one column has a certain value and based on that value set another column equal to a certain value. Any help on how to do this would be greatly appreciated.

Something Like This:

    SELECT
        td=isnull(a.[Instance_Name],''), '',
        td=isnull
            (CASE 
                WHEN a.[Instance_Name] LIKE 'ZZZ%'
                    THEN 'ZZZ'
                ELSE MAX(dbl.[Mnemonic]) 
            END AS dbl.[Mnemonic]), '',
        td=isnull(dbl.[Env_Type],''), '',
FROM #FailedCRITs a
    INNER JOIN [Util].[DBLIST] dbl ON a.[Instance_Name] = dbl.[Instance_Name]

How exactly you're trying to use the case and what problem have you faced? — Andrey Korneyev, May 15 '15 at 14:56
Case is an expression and is NOT like a switch statement you find in programming languages. It is used as a decision tree to retrieve a single value. — Sean Lange, May 15 '15 at 15:00
Simone below has the answer... Just set it literally to 'value' you want, don't try to assign it anything. — BReal14, May 15 '15 at 15:04
I tried the two suggestions below to no avail. I updated the original with the actual code. I am getting a syntax error at 'AS' now. BTW the reason for the td= etc is this code is being placed into HTML tables etc. — user3412016, May 15 '15 at 15:30
That is not an update statement. Why all the extra stuff. Post just enough to reproduce the error. — paparazzo, May 15 '15 at 15:48
I don't understand why you are using the same column name over and over and not naming the pointless empty string values. And you can't have a column conditionally be an aggregate or a scalar value. This just doesn't make any sense to me at all. — Sean Lange, May 15 '15 at 18:35

score 2 · Answer 1 · answered May 15 '15 at 15:02

2

If this is an update statement:

update table t
    set column2 = (case when column1 LIKE 'value1' then 'value2' else value3 end)

answered May 15 '15 at 15:02

Gordon Linoff

1,242,037
58
646
786

score 1 · Answer 2 · answered May 15 '15 at 14:59

1

SELECT column1, CASE
    WHEN column1 = 50 THEN 100
    ELSE 150
END AS column2
FROM Table1

answered May 15 '15 at 14:59

Simone

1,828
1
13
20

score 0 · Answer 3 · answered May 15 '15 at 17:37

It depends if you are trying to permanently update the values, or just produce them in a select statement.

if an update, it would be something like:

UPDATE tbl
SET c2 = CASE WHEN c1 LIKE 'foo' THEN 'bar' ELSE 'baz' END

else,

SELECT c1
, CASE WHEN c1 LIKE 'foo' THEN 'bar' ELSE 'baz' END AS c2
FROM TBL

T-SQL Case statement with 2 column values SQL Server

3 Answers3