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

Question

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

Answer 1

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

Answer 2

The table valued parameter isn't going to be null. Treat it more like a table and @aaraon Bertrand beat me to the punch. So yes, check for whether there are rows.

Answer 3

Workaround 1: bit parameters:

As a workaround of-sorts, I ensure each optional table-valued-parameter has an associated "ignore" parameter with a default-value of 0 so it's opt-in, e.g.

CREATE PROCEDURE dbo.UpsertNewData
    @newData1       dbo.MyDataTableType1 READONLY,
    @newData2       dbo.MyDataTableType2 READONLY,
    @ignoreNewData1 bit = 0,
    @ignoreNewData2 bit = 0
AS
SET XACT_ABORT ON; /* <-- Very important! */
IF @ignoreNewData1 <> 0
BEGIN
MERGE INTO dbo.Table1 AS tgt
FROM @newData1 AS src
-- etc


END;
IF @ignoreNewData2 <> 0
BEGIN
MERGE INTO dbo.Table2 AS tgt
FROM @newData2 AS src
-- etc


END;
RETURN 0;

...of course, if your procedure is destructive (e.g. you use MERGE with WHEN NOT MATCHED BY SOURCE THEN DELETE) then you could make it safe-by-default by making the @ignore... parameters default to 1 instead of 0.

Workaround 2: another table-valued parameter that lists ignorable parameters:

Another option, especially if you have many table-valued parameters, is to use another TVP that stores a list of parameter-names that are valid or ignorable, e.g.

CREATE TABLE dbo.ParameterNames TABLE (
    "Name" sysname NOT NULL PRIMARY KEY
);
GO
CREATE PROCEDURE dbo.UpsertNewData
    @newData1       dbo.MyDataTableType1 READONLY,
    @newData2       dbo.MyDataTableType2 READONLY,
    @useParams      dbo.ParameterNames   READONLY
AS
SET XACT_ABORT ON; /* <-- Very important! */
IF EXISTS( SELECT 1 FROM @useParams WHERE "Name" = '@newData1' )
BEGIN
MERGE INTO dbo.Table1 AS tgt
FROM @newData1 AS src
-- etc


END;
IF EXISTS( SELECT 1 FROM @useParams WHERE "Name" = '@newData2' )
BEGIN
MERGE INTO dbo.Table2 AS tgt
FROM @newData2 AS src
-- etc


END;
RETURN 0;

Workaround 3: Use #temporary tables

• If adding new parameters isn't an option, you could use a local temporary-table in a similar fashion to Workaround 2 described above (to list parameters that should be ignored, or should-not-be-ignored).
• The caller of the procedure will need to create (and drop) the temporary-table.
  • The caller can be another procedure or a raw statement batch.
  • Be sure to wrap the EXECUTE statement in aTRY/CATCH block so you can ensure the #temporaryTable will be dropped.
    • Unfortunately T-SQL doesn't have a FINALLY block, so the resultant code requires duplicating the DROP TABLE statement.
• I not a SQL Server expert so I'm unsure how this would behave with reentrant procedures (but you probably shouldn't have reentrant procedures anyway)

DECLARE @data1 dbo.MyDataTableType1;
DECLARE @data2 dbo.MyDataTableType1;
DECLARE @data3 dbo.MyDataTableType1;
CREATE TABLE #ignoreParameterNames TABLE (
    "Name" sysname NOT NULL PRIMARY KEY
);
INSERT INTO #ignoreParameterNames ( "Name" )
VALUES
( '@newData2' ),
( '@newData3' );
BEGIN TRY
DECLARE @rc int
EXECUTE @rc = dbo.UpsertNewData @newData1 = @data1, DEFAULT, DEFAULT;
IF OBJECT_ID('tempdb..#ignoreParameterNames') IS NOT NULL DROP TABLE #ignoreParameterNames;

END TRY
BEGIN CATCH
    IF OBJECT_ID('tempdb..#ignoreParameterNames') IS NOT NULL DROP TABLE #ignoreParameterNames;
    THROW;
END CATCH;