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

Niehaus

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

Amuck

Answer 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

Waldack

Answer 3

There's always exists.

For example:

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

Debauched

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

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 – Niehaus

@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. – Briard

@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... – Debauched

:( 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. – Niehaus

@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. – Debauched

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