Автоматическое логирование значений параметров процедур

При запусках хранимых часто возникает необходимость логирования их работы. Для отладки часто требуется также логировать информацию о том, с какими параметрами запускается хранимая процедура. Я уже писал о логировании в статье Лог работы sql-операторов. Однако там использовался профайлер. Если использовать эту методику, то для определения значений параметров, с которыми запускалась процедура, понадобится выполнить парсинг логируемого кода.
Есть компактный и единообразный способ логирования параметров, с которыми запускается процедура. В начале тела процедуры нужно будет наполнить временную таблицу значениями параметров процедур и вызвать специальную процедуру, которая будет записывать их лог. Процедуру логирования можно будет использовать в любой базе данных. Для начала создадим таблицы. У нас будут такие сущности: "Процедуры", "Параметры", "Запуски", "Лог".

create table dbo.Procs

(

    iProcId      int identity ( 1, 1 )      not null,

    vcProcName   nvarchar ( 450 )           not null,

    constraint PK_Procs_iProcId primary key clustered ( iProcId asc ) on [PRIMARY],

    constraint AK_Procs_vcProcName unique nonclustered ( vcProcName asc ) on [PRIMARY]

) on [PRIMARY]

go

create table dbo.Prms

(

    iProcId int                        not null,

    iPrmId  int identity ( 1, 1 )      not null,

    vcPrm   nvarchar ( 200 )           not null,

    constraint PK_Prms_iProcId_iPrmId primary key clustered ( iProcId asc, iPrmId asc ) on [PRIMARY],

    constraint AK_Prms_iProcId_vcPrm unique nonclustered ( iProcId asc, vcPrm asc ) on [PRIMARY],

    constraint FK_Prms_iProcId_Procs_iProcId foreign key ( iProcId ) references dbo.Procs ( iProcId ) on update cascade on delete no action

) on [PRIMARY]

go

create table dbo.ExecLog

(

    iProcId      int                    not null,

    iExecId      int identity ( 1, 1 )  not null,

    dtDate       datetime               not null

    constraint DF_ExecLog_dtDate default getdate (),

    constraint PK_ExecLog_iProcId_iPrmId_iExecId primary key clustered ( iProcId asc, iExecId asc ),

       constraint FK_ExecLog_iProcId_Procs_iProcId foreign key ( iPRocId ) references dbo.Procs ( iProcId ) on update cascade on delete no action

) on [PRIMARY]

go

create table dbo.PrmsLog

(

    iProcId      int          not null,

    iExecId      int          not null,

    iPrmId       int          not null,

    varPrmVal    sql_variant  null,

    constraint PK_PrmsLog_iProcId_iPrmId_iExecId primary key clustered ( iProcId asc, iExecId asc, iPrmId asc ),

    constraint FK_PrmsLog_iProcId_iExecId_ExecLog_iProcId_iExecId foreign key ( iProcId, iExecId )

        references dbo.ExecLog ( iProcId, iExecId ) on update cascade on delete no action,

    constraint FK_PrmsLog_iProcId_iPrmId_Prms_iProcId_iPrmId foreign key ( iPRocId, iPrmId )

        references dbo.Prms ( iProcId, iPrmId ) on update no action on delete no action

) on [PRIMARY]

go

Теперь напишем код процедуры логирования:

if sessionproperty ( N'quoted_identifier' ) = 0

       set quoted_identifier on

go

if sessionproperty ( N'ansi_nulls' ) = 0

       set ansi_nulls on

go

if object_id ( N'dbo.LogProcsPrm', N'P' ) is null

       exec ( N'create proc dbo.LogProcsPrm as return 1' )

go

alter proc dbo.LogProcsPrm

(

       @iDbId       int,

       @iDbProcId   int

)

as

begin

       set nocount, xact_abort on

       declare @vcProcName nvarchar ( 600 ), @iProcId int, @vcPrm nvarchar ( 200 ), @varPrmVal sql_variant, @sql nvarchar ( max ), @iPrmId int, @iExecId int

       if object_id ( N'tempdb..#DbPrms', N'U' ) is not null

             drop table #DbPrms

       create table #DbPrms

       (

             iItemId int identity ( 1, 1 )     not null,

             vcPrm  nvarchar ( 200 )           not null,

             primary key clustered ( iItemId asc ) on [PRIMARY],

             unique nonclustered ( vcPrm asc ) on [PRIMARY]

       ) on [PRIMARY]

       set @sql = '

             insert into #DbPrms ( vcPrm )

                    select name

                    from ' + quotename ( db_name ( @iDbId ) ) + '.sys.parameters

                    where [object_id] = ' + cast ( @iDbProcId as nvarchar ( 100 ) ) + '

       '

       exec sp_executesql @sql

      

       set @vcProcName = quotename ( db_name ( @iDbId ) ) + '.' + quotename ( object_schema_name ( @iDbProcId, @iDbId ) ) + '.' + quotename ( object_name ( @iDbProcId, @iDbId ) )

      

       set @iProcId = ( select iProcId

             from dbo.Procs

             where vcProcName = @vcProcName

       )

       if @iProcId is null

       begin

             insert into dbo.Procs ( vcProcName )

                    values ( @vcProcName )

             set @iProcId = scope_identity ()

       end

       insert into dbo.Prms ( iProcId, vcPrm )

             select @iProcId, dbprm.vcPrm

             from

                    #DbPrms dbprm

                           left outer join

                    dbo.Prms prm on

                           dbprm.vcPrm = prm.vcPrm and

                           prm.iProcId = @iProcId

             where prm.iPrmId is null

      

       insert into dbo.ExecLog ( iProcId )

             values ( @iProcId )

       set @iExecId = scope_identity ()

       declare cur cursor local static forward_only for

             select dbprm.vcPrm, col.varPrmVal, prm.iPrmId

             from

                    #DbPrms dbprm

                           inner join

                    #prmCol col on dbprm.iItemId = col.iItemId

                           inner join

                    dbo.Prms prm on dbprm.vcPrm = prm.vcPrm and prm.iProcId = @iProcId

       open cur

       fetch next from cur into @vcPrm, @varPrmVal, @iPrmId

       while @@fetch_status = 0

       begin

             set @sql = 'insert into dbo.PrmsLog ( iProcId, iExecId, iPrmId, varPrmVal ) values ( ' + cast ( @iProcId as nvarchar ( 100 ) ) + ', ' +

                    cast ( @iExecId as nvarchar ( 100 ) ) + ', ' + cast ( @iPrmId as nvarchar ( 100 ) ) + ', @varPrmVal )'

             exec sp_executesql @sql, N'@varPrmVal sql_variant', @varPrmVal = @varPrmVal

             fetch next from cur into @vcPrm, @varPrmVal, @iPrmId

       end

       close cur

       deallocate cur

end

go

Процедура предполагает, что создана таблица #prmCol, в которой есть столбец типа sql_variant для хранения значений параметров. Процедура определяет значения идентификатора процедуры и ее параметров из таблиц dbo.Procs и dbo.Prms. При необходимости в эти таблицы делаются вставки для ссылочной целостности. В таблицу ExecLog делается вставка записи о выполнении процедуры. Затем параметры перебираются в цикле, на каждой итерации которого делается вставка значения параметра в лог. Для перебора параметров используется временная таблица #DbPrms, которая наполняется на основе каталога sys.parameters в базе данных, которой принадлежит процедура, параметры которой логируются.

Рассмотрим теперь такую тестовую процедуру:

create proc dbo.test

(

       @prm   int,

       @data  datetime

)

as

begin

       select @prm, @data

end

go

Для логирования значений параметров этой процедуры при ее запусках, добавим в ее тело такой код:

alter proc dbo.test

(

       @prm   int,

       @data  datetime

)

as

begin

       declare @prmCol dbo.ProcPrm, @iDbId int = db_id ()

       if object_id ( N'tempdb..#prmCol', N'U' ) is not null drop table #prmCol

       create table #prmCol ( iItemId int identity ( 1, 1 ) not null, varPrmVal sql_variant null,

             primary key clustered ( iItemId asc ) on [PRIMARY] ) on [PRIMARY]

       insert into #prmCol ( varPrmVal )

             select *

             from ( values ( cast ( @prm as sql_variant ) ), ( cast ( @data as sql_variant ) ) ) data ( varPrmVal )

       exec TestDb.dbo.LogProcsPrm @iDbId, @@procid

       select @prm, @data

end

go

В начале тела процедуры создается временная таблицу, в нее кладутся значения параметров. В этом месте их требуется единственный раз перечислить, после чего идет вызов логирующей процедуры.
Для просмотра лога создадим индекс на dbo.ExecLog:
create nonclustered index IX_dtDate_iProcId_iExecId on dbo.ExecLog ( dtDate asc, iProcId asc, iExecId asc )

Теперь можно создать табличную функцию с помощью которой удобно изучать лог для определенной процедуры и за определенный период:

create function dbo.GetProcPrmLog

(

       @vcProcName         nvarchar ( 600 ),

       @dtStartDate datetime,

       @dtEndDate          datetime

)

       returns table

as

       return

             select prm.vcPrm, exlog.dtDate, prlog.varPrmVal

             from

                    dbo.Procs procs

                           inner join

                    dbo.Prms prm on procs.iProcId = prm.iProcId

                           inner join

                    dbo.ExecLog exlog on procs.iProcId = exlog.iProcId

                           inner join

                    dbo.PrmsLog prlog on

                           exlog.iProcId = prlog.iProcId and

                           exlog.iExecId = prlog.iExecId and

                           prm.iPrmId = prlog.iPrmId

             where procs.vcProcName = @vcProcName and exlog.dtDate between @dtStartDate and @dtEndDate