Início / dba / Perguntas / 134842
Accepted
mheptinstall
Asked: 2016-04-10 00:07:33 +0800 CST

Registrando Valores de Parâmetros de Procedimento Armazenado

  • 772

Já trabalhei em um ambiente SQL Server onde o log fazia parte de nossos procedimentos armazenados para capturar o início/fim da execução, valores de parâmetro e mensagens de erro que achei muito úteis e é algo que pretendo introduzir em um novo ambiente.

As tabelas usadas para esse registro pareciam algo abaixo, os parâmetros foram capturados usando INSERTinstruções em uma tabela com os valores sendo implicitamente convertidos em NVARCHAR.

CREATE TABLE dbo.Execution
(
    Id                  INT IDENTITY(1,1)           NOT NULL
,   SchemaName          NVARCHAR(128)               NOT NULL
,   ProcedureName       NVARCHAR(128)               NOT NULL
,   ExecutionStart      DATETIME                    NOT NULL
,   ExecutionEnd        DATETIME                    NULL
,   ExecutionFailed     BIT                         NOT NULL
)

CREATE TABLE dbo.ExecutionError
(
    Id                  INT IDENTITY(1,1)           NOT NULL
,   ExecutionId         INT                         NOT NULL
,   CustomErrorMessage  NVARCHAR(8000)              NULL
,   SqlErrorMessage     NVARCHAR(8000)              NULL
)

CREATE TABLE dbo.ExecutionParameter
(
    Id                  INT IDENTITY(1,1)           NOT NULL
,   ExecutionId         INT                         NOT NULL
,   ParameterName       NVARCHAR(128)               NOT NULL
,   ParameterValue      NVARCHAR(MAX)               NULL
)

Tenho reconsiderado a ExecutionParametertabela com a possibilidade de usar um SQL_VARIANTtipo de dados para que eu possa obter o tipo de dados base, se necessário, para fins de análise e relatório, sem ter que adivinhar com base no nome/valor.

No entanto, isso não funcionaria para parâmetros que possuem um tipo de dados de NVARCHAR(MAX)ou VARCHAR(MAX)e, portanto, precisariam ainda ter a NVARCHAR(MAX)coluna lá, o que seria NULLa maior parte do tempo.

O uso de SQL_VARIANTé tentador, mas sinto que a estrutura original da tabela funciona bem e não pode ser melhorada sem tornar o processo mais complicado.

Isso é algo que você fez anteriormente, em caso afirmativo, como você o implementou? Você pode ver espaço para melhorias no esquema acima sem torná-lo excessivamente complicado?

Talvez uma tabela adicional com detalhes do procedimento armazenado e seus parâmetros que possam ser referenciados? Embora eu ache que isso seria difícil de manter e tornar-se confuso, pois os procedimentos armazenados são modificados com o tempo.

sql-server stored-procedures

1 respostas

  1. Best Answer

    Sim, fizemos esse tipo de coisa em uma empresa em que trabalhei há alguns anos. Embora tenhamos feito isso apenas para registro de erros, era o mesmo conceito geral. Você só precisa escrever a instrução SELECT que inclui todos os parâmetros de entrada, usar FOR XML, e armazená-la em uma variável que pode ser inserida em sua Executiontabela em um novo campo: Parameters XML NULL. E então você pode descartar a ExecutionParametermesa.

    Tente o seguinte, pois ele mostra três maneiras de gerar o XML: baseado em atributo, baseado em elemento e baseado em elemento, incluindo elementos NULL. O comportamento padrão de FOR XMLé indicar NULLvalores não incluindo o atributo ou elemento. Mas se você estiver fazendo XML baseado em elemento e quiser que os parâmetros sejam exibidos mesmo que sejam NULL, será necessário especificar XSINILna FOR XMLcláusula. A capacidade de incluir um indicador para NULLnão está disponível em XML baseado em atributo.

    Observe que não há uma grande diferença entre o XML baseado em atribuição e o XML baseado em elemento padrão (padrão = não inclui NULLitens) em termos de tamanho de armazenamento (conforme mostrado no exemplo abaixo). Ao visualizar o XML, parece que há muito mais "inchaço" com base em elementos, e esse seria o caso se os dados fossem armazenados em um NVARCHARcampo ou arquivo de texto. Mas o tipo de XMLdados usa um método otimizado para armazenar os dados internamente, de uma forma que não pode ser vista, mas é claramente indicada na DATALENGTHsaída resultante.

    Há, no entanto, um pequeno aumento de tamanho ao usar a XSINILopção de XML baseado em elementos e precisa acompanhar os NULLelementos que são excluídos dos outros dois tipos. O aumento no tamanho é devido a uma ocorrência única para declarar o xsinamespace no elemento raiz e, em seguida, uma ocorrência por NULLelemento.

    DECLARE @Param1 INT = 5,
        @Param2 DATETIME = NULL,
        @Param3 NVARCHAR(50) = N'Test < some & XML chars "',
        @Param4 DATETIME = '2016-04-09';

DECLARE @Parameters XML;

SELECT @Parameters = (
   SELECT @Param1 AS [Param1],
          @Param2 AS [Param2],
          @Param3 AS [Param3],
          @Param4 AS [Param4]
   FOR XML RAW(N'Params') -- Attribute-based XML (NULL attributes are missing)
);

SELECT @Parameters, DATALENGTH(@Parameters); -- 183

SELECT @Parameters = (
   SELECT @Param1 AS [Param1],
          @Param2 AS [Param2],
          @Param3 AS [Param3],
          @Param4 AS [Param4]
   FOR XML RAW(N'Params'), ELEMENTS -- Element-based XML (NULL elements are missing)
);

SELECT @Parameters, DATALENGTH(@Parameters); -- 185

SELECT @Parameters = (
   SELECT @Param1 AS [Param1],
          @Param2 AS [Param2],
          @Param3 AS [Param3],
          @Param4 AS [Param4]
   FOR XML PATH(N'Params'), ELEMENTS XSINIL -- Element-based XML (NULL elements included)
);

SELECT @Parameters, DATALENGTH(@Parameters); -- 434

    Valores XML retornados:

    <!-- Attribute-based -->
<Params Param1="5" Param3="Test &lt; some &amp; XML chars &quot;"
 Param4="2016-04-09T00:00:00" />


<!-- Element-based -->
<Params>
  <Param1>5</Param1>
  <Param3>Test &lt; some &amp; XML chars "</Param3>
  <Param4>2016-04-09T00:00:00</Param4>
</Params>


<!-- Element-based including NULL elements -->
<Params xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <Param1>5</Param1>
  <Param2 xsi:nil="true" />
  <Param3>Test &lt; some &amp; XML chars "</Param3>
  <Param4>2016-04-09T00:00:00</Param4>
</Params>

    Notas Adicionais:

    • Para as colunas SchemaNamee ProcedureName, eu usaria sysnamecomo o tipo de dados, pois é assim que é definido nas tabelas do sistema. E certifique-se de usar letras minúsculas sysname, pois é um alias que reside mastere os servidores com um agrupamento padrão binário ou com distinção entre maiúsculas e minúsculas não poderão encontrar esse alias se não estiver todo em letras minúsculas. Seu servidor atual pode não diferenciar maiúsculas de minúsculas, mas ainda é um bom hábito adquirir ao usar arquivos sysname.

    • Para a ExecutionErrortabela, certifique-se de incluir INTcolunas para capturar: ERROR_LINE(), ERROR_NUMBER(), ERROR_STATE()e ERROR_SEVERITY().

    • Não, você não deseja usar SQL_VARIANT, pois isso o impede de armazenar os tipos de LOB: VARCHAR(MAX), NVARCHAR(MAX), VARBINARY(MAX)e XML.

    • Não, você não pode usar NVARCHAR(8000)porque NVARCHARtem um tamanho máximo de 4000, a menos que você use MAX.

    • 1

relate perguntas