Have you ever needed to know how often a stored procedure is called, or whether it is used at all? This is easy to find out within the database, but of course an unknown number of outside applications may check in from time to time and they may be have been written by end users. It would be useful to be able to switch on logging of executions for a period of time (prior to an upgrade of the database for example) so that obsolete code can be removed and problems pre-empted.

The first problem with logging code is that it’s clutter, so you’ll want to encapsulate it in a separate procedure, which you call in one line of source code. The second is that it’s boring; if you’re retro-fitting logging code it’s just copy and paste, but if the call uses a literal for the name of the stored procedure, you’re likely to forget to edit each time you paste it. You don’t want to have to test that the code is done correctly, or worse still, find out after a month that a procedure is logging under the wrong name.

So the code that calls the helper procedure is a one-liner and doesn’t need to be edited. Hope it’s some help. Please note: I have not added any logic to determine levels of logging verbosity, but the design allows for it. This is not production-ready code and does not cover every potential issue, please use it as a guideline only.

/*
DROP PROCEDURE udpBusinessProc
DROP TABLE ExecLog
DROP PROCEDURE udpExecLog
DROP TABLE LogLevel
*/

--create single-row table as a "constant" to set database logging level--
CREATE TABLE LogLevel
(
/*
column check constraint and primary key ensure that table will only contain one row--
primary key prevents multiple rows with value 1 being inserted and disallows Nulls
*/
EnforceRowCount int DEFAULT(1) CHECK(EnforceRowCount = 1)
PRIMARY KEY(EnforceRowCount)
,Setting int CHECK(Setting Between 0 And 3)
--enforce a single row--
)


--as a test set logging on--
INSERT INTO
LogLevel
(
Setting
)
VALUES
(
1
)

--try to insert another row with logging off--
INSERT INTO
LogLevel
(
Setting
)
VALUES
(
0
)

--didn't work, we have enforced a single-row rule; update instead--
UPDATE LogLevel SET Setting = 0

--log table--
CREATE TABLE ExecLog
(
LogId int IDENTITY(1,1)
,ProcName sysname NOT NULL
,Msg nvarchar(50) NULL
,WriteDateTime datetimeoffset DEFAULT(sysdatetimeoffset())
,PRIMARY KEY(WriteDateTime, LogId)
)


GO


--the logging procedure--
CREATE PROCEDURE udpExecLog
@i_ProcName sysname
,@i_Msg nvarchar(50)
,@i_LogLevel int
AS
BEGIN
--write log if switched on locally or if global setting requires it--
IF @i_LogLevel > 0 Or (SELECT MAX(Setting) FROM LogLevel) > 0
BEGIN
INSERT INTO ExecLog
(
ProcName
,Msg
)
VALUES
(
@i_ProcName
,@i_Msg
)
END


END


GO



CREATE PROCEDURE udpBusinessProc
@i_Debug int = 0
AS
BEGIN
DECLARE @Msg nvarchar(140) = 'Start of' + ' ' + OBJECT_NAME(@@PROCID), @ProcName sysname = OBJECT_NAME(@@PROCID); EXEC udpExecLog @ProcName, @Msg, @i_Debug;
SELECT 'Now do some useful work'
PRINT 'Now do some useful work'


END


GO


EXEC udpBusinessProc 1

SELECT * FROM ExecLog