SET value in the WHERE clause

Hopeless Published at Dev

hopeless

How can i set the value in the WHERE clause. I'm using SQL Server MS.

DECLARE @Value INT;

SOMEPOINT:

INSERT $(TempTable)(Value, ...)
SELECT DISTINCT @Value, ...
FROM $(TempTable) t1
JOIN $(TempTable) t2 ON 
    t2.smth1 = t1.smth2

WHERE
    @Value = CASE WHEN t1.Value IS NULL THEN t2.Value ELSE t1.Value // so i want here to set the @Value value instead of ckecking it
        END

IF @@ROWCOUNT > 0 GOTO SOMEPOINT

I've tried a lot of things, nothing helped. If it is impossible to do this inside of WHERE clause what should i change?

Sergey Kalinichenko

You cannot set variables inside WHERE clause, but you can use the expression that computes the value:

INSERT $(TempTable)(Value, ...)
SELECT DISTINCT 
    CASE WHEN t1.Value IS NULL THEN t2.Value ELSE t1.Value END AS Value
, ...
FROM $(TempTable) t1
JOIN $(TempTable) t2 ON 
    t2.smth1 = t1.smth2

This makes Value a "computed column" of your SELECT DISTINCT clause, making it eligible for insertion.

Note: COALESCE is more appropriate in situations when you provide a default value for NULL (Gordon Linoff)

INSERT $(TempTable)(Value, ...)
SELECT DISTINCT 
    COALESCE(t1.Value, t2.Value) AS Value
, ...
FROM $(TempTable) t1
JOIN $(TempTable) t2 ON 
    t2.smth1 = t1.smth2

Collected from the Internet

Please contact [email protected] to delete if infringement.