主页 / dba / 问题 / 134842
Accepted
mheptinstall
Asked: 2016-04-10 00:07:33 +0800 CST

记录存储过程参数值

  • 772

我曾在 SQL Server 环境中工作过，在此之前，日志记录已成为我们存储过程的一部分，以捕获执行开始/结束、参数值和错误消息，我发现它们非常有用，并且我希望在新环境中引入这些内容。

用于此日志记录的表如下所示，使用INSERT语句将参数捕获到表中，其中的值被隐式转换为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
)

我一直在重新考虑ExecutionParameter使用SQL_VARIANT数据类型的可能性的表格，因此如果需要分析和报告目的，我可以获得基本数据类型，而不必根据名称/值进行猜测。

但是，这不适用于数据类型为NVARCHAR(MAX)or的参数，因此在大多数情况下VARCHAR(MAX)仍需要该NVARCHAR(MAX)列。NULL

的使用SQL_VARIANT很诱人，但我觉得原始表结构工作正常，如果不使过程更复杂，就不能真正做得更好。

这是您以前做过的事情，如果是，您是如何实现的？你能看到上述模式的改进空间而不使其过于复杂吗？

也许还有一个包含存储过程详细信息及其参数的附加表，可以参考？虽然我觉得随着时间的推移存储过程被修改，这将难以维护并且变得混乱。

sql-server stored-procedures

1 个回答

  1. Best Answer

    是的，我们在几年前工作的一家公司做过这种事情。尽管我们只为错误记录而这样做，但它是相同的一般概念。您只需要编写包含所有输入参数的 SELECT 语句，使用FOR XML并将其存储在一个变量中，该变量可以插入到您的Execution表中的新字段中：Parameters XML NULL. 然后你可以报废ExecutionParameter桌子。

    尝试以下操作，因为它显示了生成 XML 的三种方式：基于属性、基于元素和基于元素（包括 NULL 元素）。的默认行为是通过不包括属性或元素FOR XML来指示值。NULL但是，如果您正在执行基于元素的 XML 并希望参数显示即使它们是NULL，那么您需要XSINIL在FOR XML子句中指定。NULL在基于属性的 XML 中不提供包含指示符的功能。

    请注意，基于属性的 XML 和默认的基于元素的 XML（默认 = 不包括项目）在存储大小方面没有太大区别NULL（如下例所示）。在查看 XML 时，确实会出现基于元素的更多“膨胀”，如果将数据存储在NVARCHAR字段或文本文件中就会出现这种情况。但是XML数据类型使用了一种优化的方法在内部存储数据，这种方式看不到，但在结果DATALENGTH输出中清楚地表明了这一点。

    但是，使用基于元素的 XML 选项时大小会略有增加，XSINIL并且它需要跟踪NULL从其他两种类型中排除的元素。大小的增加是由于在根元素中声明命名空间的一次性命中xsi，然后是每个NULL元素的命中。

    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

    返回的 XML 值：

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

    补充说明：

    • 对于SchemaName和ProcedureName列，我将使用sysname它作为数据类型，因为它是在系统表中定义的。并且一定要使用小写，sysname因为它是一个存在的别名，master并且如果它不是全部小写，则具有二进制或区分大小写的默认排序规则的服务器将无法找到该别名。您当前的服务器可能不区分大小写，但在使用sysname.

    • 对于ExecutionError表格，请务必包含INT要捕获的列：ERROR_LINE()、ERROR_NUMBER()、ERROR_STATE()和ERROR_SEVERITY()。

    • 不，您不想使用SQL_VARIANT它，因为这会阻止您存储 LOB 类型：VARCHAR(MAX)、NVARCHAR(MAX)、VARBINARY(MAX)和XML.

    • 不，您不能使用NVARCHAR(8000)，因为NVARCHAR最大尺寸为 4000，除非您使用MAX.

    • 1

相关问题