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

sql-server tsql null table-variable

31,944

Solution 1

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 ) )
)

Solution 2

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

Solution 3

There's always exists.

For example:

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

31,944

Author by

Eric Yin

Updated on March 11, 2020

Comments

Eric Yin over 4 years
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 ) )
)
```
Martin Smith over 12 years

+1 EXISTS can be more efficient than COUNT as it stops after the first row.
Eric Yin over 12 years

Thanks, I tried the exist but it cannot used inside a select condition: AND ( ( I CANNOT PLACE Exist HERE ) OR ( item IN ( SELECT T FROM @dataInTable ) ) ). Do you know the correct syntax
Martin Smith over 12 years

@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 12 years

@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...
Eric Yin over 12 years

:( My bad, I wrote EXIST(select 1 from @dataInTable) and got error. I have no idea why it shows as blue in my management studio, so I did not find this typo.
Factor Mystic over 12 years

@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 12 years

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