Setting BIT variable using CASE statements

Question

I'm in the middle of a migration that uses some reporting elements from Crystal and attempting to convert what existed previously into SQL. It looks to use VB scripting but I can't figure out how to make the switch over.

Initially I'd assume a CASE WHEN statement would suffice but I can't determine the right logic behind the query.

Sample of the VB below:

Dim HasValue As Boolean

If isnull({Reference.Shallow}) Then
    HasValue=False
    formula="MISSING"
Else 
    HasValue=True
End If

I am aware that IF does exist in SQL Server but when researching it, people tend to stray away from it as CASE WHEN does the same?

I have some psuedo that I'd imagine should work in the same way but this does not resolve as you cannot set a variable within a CASE WHEN statement (I believe):

DECLARE HasValue BIT

CASE WHEN Reference.Shallow IS NULL 
THEN SET @HasValue = 1 
ELSE SET @HasValue = 0 
END AS Shallow

What would be the most appropriate way of doing this within SQL?

In SQL case is an expression, not a logical flow/branching construct. Try using set @HasValue = CASE ... END. — AlwaysLearning
– AlwaysLearning, Commented Sep 16, 2020 at 14:27
@Larnu think you mis-read the question, I know IF exists in SQL! — ExitGame
– ExitGame, Commented Sep 16, 2020 at 14:41
I miss read that statement, but not the question, @ExitGame . — Thom A
– Thom A ♦, Commented Sep 16, 2020 at 14:42

mohade3 lotfi · Accepted Answer · 2020-09-19 10:50:28Z

3

You must use this code snippet

DECLARE @HasValue BIT

SELECT @HasValue=CASE WHEN Shallow IS NULL THEN CAST(1 AS BIT) ELSE CAST(0 AS 
BIT) END FROM Reference.Shallow

edited Sep 19, 2020 at 10:50

answered Sep 19, 2020 at 10:32

mohade3 lotfi

512 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Thom A · Accepted Answer · 2020-09-16 14:28:40Z

0

CASE returns a scalar value, not a operation or boolean result. You set the value of the variable to the result of the CASE:

SET @HasValue = (SELECT CASE WHEN R.Shallow IS NULL THEN 1 ELSE 0 END
                 FROM dbo.Reference R
                 /*WHERE...? */);

answered Sep 16, 2020 at 14:28

Thom A♦

97.7k12 gold badges67 silver badges102 bronze badges

Collectives™ on Stack Overflow

Setting BIT variable using CASE statements

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related