HiveBrain v1.2.0
Get Started
← Back to all entries
snippetsqlMajor

How do I check for a null or empty table-valued parameter?

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
nullvaluedemptyparameterforhowchecktable

Problem

I have a stored procedure (SS2k8) with a couple table-valued parameters that will sometimes be null or empty. I have seen this StackOverflow post that says that null/empty TVPs should simply be omitted from the calling parameter list. My problem is that I can't figure out how to check for empty or null inside the stored procedure as "IF (@tvp IS NULL)" fails on procedure creation with the message 'Must declare the scalar variable "@tvp"'. Do I have to do a SELECT COUNT(*) on the TVP and check for zero?

Code excerpt:

CREATE PROCEDURE [foo] (@tvp [TvpType] READONLY) AS

IF (@tvp IS NOT NULL) -- doesn't work
BEGIN
  -- lots of expensive processing
END
ELSE
BEGIN
  -- a little bit of cheap processing
END
...

Solution

A table can't be NULL, nor can a TVP. How do you check if a table is empty? You certainly don't say IF Sales.SalesOrderHeader IS NULL. :-)

IF EXISTS (SELECT 1 FROM @tvp)
BEGIN
  -- lots of expensive processing
END
ELSE
BEGIN
  -- a little bit of cheap processing
END
...

Code Snippets

IF EXISTS (SELECT 1 FROM @tvp)
BEGIN
  -- lots of expensive processing
END
ELSE
BEGIN
  -- a little bit of cheap processing
END
...

Context

StackExchange Database Administrators Q#30754, answer score: 22

Revisions (0)

No revisions yet.