如何检查空或空表值参数？

Question

我有一个存储过程(SS2k8)，它有两个表值参数，有时为空或空。我已经看到这个StackOverflow邮政，它说应该从调用参数列表中省略空/空的TVP。我的问题是，我无法理解如何检查存储过程中的空值或空值，就像"IF (@tvp为空)“在过程创建时失败一样，消息‘必须声明标量变量"@tvp"’。我必须在TVP上做一个选择计数(*)并检查为零吗？

代码摘录：

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

一个表不能是NULL，TVP也不能。如何检查一张桌子是否是空的？你当然不会说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

表值参数不会为空。把它当成一张桌子，@把我打得头破血流。所以是的，检查是否有行。

Answer 3

解决方案1：bit参数：

作为一种解决方法，我确保每个可选表值-参数都有一个与默认值0相关联的“忽略”参数，因此它可以被选中。

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课程，如果您的过程是破坏性的(例如，您将MERGE与WHEN NOT MATCHED BY SOURCE THEN DELETE一起使用)，那么您可以通过将@ignore...参数默认为1而不是0来使其安全。

解决方案2:另一个列出可忽略参数的表值参数：

另一个选项，特别是当您有许多表值参数时，是使用另一个TVP来存储有效或可忽略的参数名称列表。

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;

解决方案3:使用#temporary表

• 如果添加新参数不是一个选项，您可以使用一个局部临时桌与上面描述的工作方法2类似(列出应该被忽略或不应该被忽略的参数)。
• 过程的调用者将需要创建(并删除)临时表。
  • 调用者可以是另一个过程或原始语句批处理。
  • 确保将EXECUTE语句包装在TRY/CATCH块中，这样就可以确保删除#temporaryTable。
    • 不幸的是，T没有FINALLY块，因此生成的代码需要复制DROP TABLE语句。

• 我不是Server专家，所以我不确定这会如何处理重入过程(但您可能不应该使用重入过程)。

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;