DaveWentzel.com            All Things Data

Stored Procedure Logging

 

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS OFF

GO


create proc p$report_log @log_id   int    = 0 output

           , @row_count   int    = 0    /* entryid or row_count */

           , @procname       varchar(25) = null

        , @start_int   int   = 0

        , @end_int   int   = 0

        , @start_date  datetime = null

        , @end_date   datetime = null

        , @officekey      varchar(12) = null

        , @deptkey        varchar(12) = null

        , @costcenterkey int  = null

        , @currencyid  int   = null

           , @summaryid      int         = null

        , @approvalperiod int   = null

        , @clientid   int   = null

        , @projectid   int   = null

        , @reportname       varchar(25) = null
as

if @log_id      = 0

and @procname        is null

 return

if @log_id           != 0

begin

 declare @endtime   datetime

 select @endtime    = getdate()
 update t$report_log

    set elapsed_ms   = datediff(ms, starttime, @endtime)

      , row_count   = @row_count

  where log_id    = @log_id
 update t$report_log

    set cpu     = p.cpu - t$report_log.cpu

   , physical_io   = p.physical_io - t$report_log.physical_io

   from master..sysprocesses p

  where t$report_log.log_id = @log_id

    and p.spid    = @@spid

 return

end

declare @officeid  int

      , @departmentid int
select @officeid  = null

     , @departmentid = null
/* office and department key always have a percent at the end */

if datalength (@officekey) > 0  and  right (@officekey, 1) = '%'

 select @officekey = substring (@officekey, 1, datalength(@officekey) - 1)

if datalength (@deptkey) > 0  and  right (@deptkey, 1) = '%'

 select @deptkey = substring (@deptkey, 1, datalength(@deptkey) - 1)
select @officeid  = id

  from hier$rel

 where type    = 10

   and orgkeycode  = @officekey
select @departmentid = id

  from hier$rel

 where type    = 20

   and orgkeycode  = @officekey
if @start_int != 0

 select @start_date = dateadd(dd,@start_int,'1/1/1989')

if @end_int != 0

 select @end_date = dateadd(dd,@end_int,'1/1/1989')
if @start_int = 0 and @start_date is null

begin

 if @summaryid is not null

  select @start_date = dateadd(dd, startdate,'1/1/1989')

       , @end_date = dateadd(dd, enddate,'1/1/1989')

    from summaryperiod

   where summaryid = @summaryid

 else if @approvalperiod is not null

  select @start_date = dateadd(dd, startdate,'1/1/1989')

       , @end_date = dateadd(dd, enddate,'1/1/1989')

    from approvalperiod

   where startdate = @approvalperiod

end
declare @objectid   int

      , @starttime  datetime

   , @cpu   int

   , @physical_io int

   , @entryid  int

   , @workstation varchar(12)
select @entryid   = @row_count
select @objectid  = id

  from sysobjects

 where name    = @procname
select @starttime  = getdate()
select @cpu    = cpu

     , @physical_io  = physical_io

  , @workstation  = hostname

  from master..sysprocesses

 where spid    = @@spid
insert t$report_log

select @reportname

     , @procname

  , @@spid

  , @objectid

  , @workstation

  , @entryid

  , @starttime

  , 0

  , @cpu

  , @physical_io

  , 0

  , @start_date

  , @end_date

  , @officeid

  , @departmentid

  , @costcenterkey

  , @summaryid

  , @approvalperiod

  , @clientid  

  , @projectid 

  , @currencyid
select @log_id   = @@IDENTITY


GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO
 
 

Add new comment