Menu
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 ) )
)
958
First, the ISNULL function checks whether the parameter value is NULL or not. If True, it will replace the value with Empty string or Blank. Next, IIF will check whether the parameter is Blank or not. If true, Occupation = Occupation otherwise, Occupation = User-provided result.
IF EXISTS can be used for check if a table is empty.
A null database field means that there is no value for a given record. It indicates the absence of a value. A blank database field means that there is a value for a given record, and this value is empty (for a string value) or 0 (for a numeric value).
As the User Define table Types are created as table-valued, so you cannot assign null to a table.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
