При запусках хранимых часто возникает необходимость логирования их работы. Для отладки часто требуется также логировать информацию о том, с какими параметрами запускается хранимая процедура. Я уже писал о логировании в статье Лог работы sql-операторов. Однако там использовался профайлер. Если использовать эту методику, то для определения значений параметров, с которыми запускалась процедура, понадобится выполнить парсинг логируемого кода.
Есть компактный и единообразный способ логирования параметров, с которыми запускается процедура. В начале тела процедуры нужно будет наполнить временную таблицу значениями параметров процедур и вызвать специальную процедуру, которая будет записывать их лог. Процедуру логирования можно будет использовать в любой базе данных. Для начала создадим таблицы. У нас будут такие сущности: "Процедуры", "Параметры", "Запуски", "Лог".
Теперь напишем код процедуры логирования:
Процедура предполагает, что создана таблица #prmCol, в которой есть столбец типа sql_variant для хранения значений параметров. Процедура определяет значения идентификатора процедуры и ее параметров из таблиц dbo.Procs и dbo.Prms. При необходимости в эти таблицы делаются вставки для ссылочной целостности. В таблицу ExecLog делается вставка записи о выполнении процедуры. Затем параметры перебираются в цикле, на каждой итерации которого делается вставка значения параметра в лог. Для перебора параметров используется временная таблица #DbPrms, которая наполняется на основе каталога sys.parameters в базе данных, которой принадлежит процедура, параметры которой логируются.
Рассмотрим теперь такую тестовую процедуру:
Для логирования значений параметров этой процедуры при ее запусках, добавим в ее тело такой код:
В начале тела процедуры создается временная таблицу, в нее кладутся значения параметров. В этом месте их требуется единственный раз перечислить, после чего идет вызов логирующей процедуры.
Для просмотра лога создадим индекс на dbo.ExecLog:
create nonclustered index IX_dtDate_iProcId_iExecId on dbo.ExecLog ( dtDate asc, iProcId asc, iExecId asc )
Теперь можно создать табличную функцию с помощью которой удобно изучать лог для определенной процедуры и за определенный период:
Есть компактный и единообразный способ логирования параметров, с которыми запускается процедура. В начале тела процедуры нужно будет наполнить временную таблицу значениями параметров процедур и вызвать специальную процедуру, которая будет записывать их лог. Процедуру логирования можно будет использовать в любой базе данных. Для начала создадим таблицы. У нас будут такие сущности: "Процедуры", "Параметры", "Запуски", "Лог".
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
