T-SQL: check if variable is null

Question

I'm wondering if there is a way to check if variable @varChar is NULL and variable @bit is 0.

My attempt:

if ISNULL(@varChar, false) and (@bit = 0) 
begin
    RAISERROR ('varchar was not specified', 16,1);
end

My problem is that ISNULL(check_if_null, replacement_value) does not work like this, and I can not find an alternative.

Lukasz Szozda · Accepted Answer · 2018-07-03 15:47:30Z

14

You could use IS NULL:

IF @varCHAR IS NULL AND @bit = 0
BEGIN
    RAISERROR ('varchar was not specified', 16,1);
END

Another approach is:

DECLARE @varCHAR VARCHAR(MAX) = NULL;

IF ISNULL(@varCHAR,'false') = 'false' AND @bit = 0
    RAISERROR ('varchar was not specified', 16,1);

edited Jul 3, 2018 at 15:47

answered Jul 3, 2018 at 15:42

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

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

2 Comments

HABO Over a year ago

The first solution checks for NULL. The second checks for a magic value that may or may not occur in the data ('false'). Best practice is to avoid magic values.

Lukasz Szozda Over a year ago

@HABO I agree that we should avoid "magic" values at all cost and first approach is preferred. I just wanted to show OP that using ISNULL(@varChar, false) is possible.

EzLo · Accepted Answer · 2018-07-03 15:42:40Z

1

Use IS NULL when checking if a value is null.

IF @varChar IS NULL and @bit = 0 
    RAISERROR ('varchar was not specified', 16,1);

answered Jul 3, 2018 at 15:42

EzLo

14.2k10 gold badges38 silver badges40 bronze badges

Collectives™ on Stack Overflow

T-SQL: check if variable is null

2 Answers 2

2 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Related