How to check if a table variable is empty in SQL Server?

Question

This is a section of one of my stored procedure:

@dataInTable dbo.Table_Variable readonly,
....

AND (
    ( @dataInTable IS NULL )
    OR
    ( item IN ( SELECT T FROM @dataInTable ) )
)

@dataInTable IS NULL is wrong in syntax, error is

Must declare the scalar variable "@dataInTable"

So I change it to:

(SELECT T FROM @dataInTable) IS NULL

This works but if @dataInTable has more than 1 item, I get an error:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Understandable, so I change it to:

(SELECT TOP(1) T FROM @ProgramRatings) IS NULL

Works perfectly, what I have is performance concern.

I am wondering, if there has an easier way to check whether a table variable is empty, like

AND (
    ( @dataInTable IS EMPTY )
    OR
    ( item IN ( SELECT T FROM @dataInTable ) )
)

Phil Klein · Accepted Answer · 2015-03-10 19:01:18Z

36

Table variables are different from scalar variables so @dataInTable IS NULL is not allowed as you mentioned. I would suggest the following:

EXISTS (SELECT 1 FROM @dataInTable)

The following also may make sense for your situation if you actually care to know the count:

DECLARE @dataCount int = (SELECT COUNT(1) FROM @dataInTable)
IF @dataCount = 0 -- Empty

edited Mar 10, 2015 at 19:01

answered Jan 5, 2012 at 18:26

Phil Klein

7,5143 gold badges34 silver badges33 bronze badges

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

Comments

Factor Mystic · Accepted Answer · 2012-01-06 03:04:24Z

11

There's always exists.

For example:

select 'Yep'
where exists (select 1 from @dataInTable)

edited Jan 6, 2012 at 3:04

answered Jan 5, 2012 at 18:28

Factor Mystic

26.9k16 gold badges82 silver badges96 bronze badges

7 Comments

Martin Smith Over a year ago

+1 EXISTS can be more efficient than COUNT as it stops after the first row.

Martin Smith Over a year ago

@EricYin You are probably looking for CASE if you want to use it in a SELECT. Difficult to know what you are doing based on the fragments you have posted so far.

Factor Mystic Over a year ago

@EricYin I don't see why you can't use exists as a drop in replacement for what you currently have posted in your question: @dataInTable IS EMPTY is replaced with exists (select 1 from @dataInTable) if this is not the case you should add more context to your question...

Factor Mystic Over a year ago

@EricYin You do see that it's exists ending with an s, right? You've said "exist" twice now, and that would not be correct.

Martin Smith Over a year ago

@EricYin - Doesn't show up blue for me. Sure you didn't type EXIT? Maybe your S key is wearing out!

|

Oleg Dok · Accepted Answer · 2012-01-05 18:48:21Z

7

For check if table variable is empty, just use EXISTS as already mentioned by other people,

but

if you differ empty set from unknown set - then you have no choice - you have to introduce extra variable which states if empty set is really empty or unknown.

declare @dataInTableIsUnknown BIT

...

AND (
    ( @dataInTableIsUnknown = 1 )
    OR
    ( item IN ( SELECT T FROM @dataInTable ) )
)

answered Jan 5, 2012 at 18:48

Oleg Dok

21.9k4 gold badges48 silver badges55 bronze badges

Collectives™ on Stack Overflow

How to check if a table variable is empty in SQL Server?

3 Answers 3

Comments

7 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

7 Comments

Comments

Related