15

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

3 Answers 3

19

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
...
2
  • 2
    So, what is the good way of writing the queries that assume we want to select data using the TVP but if we don't pass data we want to treat it as no filter? Are we back to (value in (select col from @TVP) or not exists (select 1 from @TVP)) ?
    – Naomi
    Jul 21, 2020 at 22:54
  • 1
    @Naomi The best fix I've come across so-far is to add another parameter (for each table-valued parameter) indicating if the TVP should be ignored, with a default value of 0 so it's opt-in, e.g. @tbp TvpType READONLY, @ignoreTvp bit = 0.
    – Dai
    Mar 2 at 1:14
3

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.

0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.