Checking for null,empty variable in sql server

Question

I always endup doing this to cover all situations where @variable can be empty or null,

 IF(COALESCE(@variable,'') <> '')
    BEGIN


    END

Is this the most optimal way to check this?

Lamak · Accepted Answer · 2011-10-20 17:31:34Z

4

If its a variable, then it shouldn't matter. If you are doing a similar filter on a column of a table, then I would recommend: WHERE Column IS NOT NULL OR Column <> '' as it mantains the use of a possible index on that column.

answered Oct 20, 2011 at 17:31

Lamak

70.8k12 gold badges118 silver badges119 bronze badges

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

2 Comments

Alex J Over a year ago

what you mean by it does not matter on a variable?

Rick Over a year ago

He means there is no performance impact for variables, when compared to separating the IS NULL test from the <> '' test, because variables aren't indexed.

nvogel · Accepted Answer · 2011-10-20 19:36:30Z

1

Assuming @variable is a VARCHAR/NVARCHAR then the following should do the same job:

IF @variable > ''
    BEGIN
    ...    
    END

answered Oct 20, 2011 at 19:36

nvogel

25.6k3 gold badges47 silver badges89 bronze badges

Collectives™ on Stack Overflow

Checking for null,empty variable in sql server

2 Answers 2

2 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Related