Início / dba / Perguntas / 30754
Accepted
Dan
Asked: 2012-12-20 08:35:55 +0800 CST

Como faço para verificar um parâmetro com valor de tabela nulo ou vazio?

  • 772

Eu tenho um procedimento armazenado (SS2k8) com alguns parâmetros com valor de tabela que às vezes serão nulos ou vazios. Eu vi este post do StackOverflow que diz que TVPs nulos/vazios devem simplesmente ser omitidos da lista de parâmetros de chamada. Meu problema é que não consigo descobrir como verificar vazio ou nulo dentro do procedimento armazenado, pois "IF (@tvp IS NULL)" falha na criação do procedimento com a mensagem 'Deve declarar a variável escalar "@tvp"'. Eu tenho que fazer um SELECT COUNT(*) no TVP e checar por zero?

Trecho do código:

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
...
sql-server sql-server-2008

3 respostas

  1. Best Answer

    Uma mesa não pode ser NULL, nem um TVP. Como verificar se uma tabela está vazia? Você certamente não diz 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
...
    • 19

  2. O parâmetro com valor de tabela não será nulo. Trate-o mais como uma mesa e @aaraon Bertrand me venceu. Então, sim, verifique se há linhas.

    • 3

  3. Solução alternativa 1: bitparâmetros:

    Como uma solução alternativa, garanto que cada parâmetro opcional com valor de tabela tem um parâmetro "ignorar" associado com um valor padrão de, 0portanto, é opt-in, por exemplo

    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;

    ... claro, se o seu procedimento for destrutivo (por exemplo, você usa MERGEcom WHEN NOT MATCHED BY SOURCE THEN DELETE), então você pode torná-lo seguro por padrão, tornando os @ignore...parâmetros padrão em 1vez de 0.

    Solução alternativa 2: outro parâmetro com valor de tabela que lista parâmetros ignoráveis:

    Outra opção, especialmente se você tiver muitos parâmetros com valor de tabela, é usar outro TVP que armazene uma lista de nomes de parâmetros válidos ou ignoráveis, por exemplo

    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;

    Solução alternativa 3: use #temporarytabelas

    • Se adicionar novos parâmetros não for uma opção, você pode usar uma tabela temporária local de maneira semelhante à solução alternativa 2 descrita acima (para listar os parâmetros que devem ser ignorados ou não devem ser ignorados).
    • O chamador do procedimento precisará criar (e descartar) a tabela temporária.
    • Eu não sou um especialista em SQL Server, então não tenho certeza de como isso se comportaria com procedimentos reentrantes (mas você provavelmente não deveria ter procedimentos reentrantes de qualquer maneira)
    
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;
    • 0

relate perguntas