In case this helps anyone, here is how I do in-SPROC logging. In addition to the normal stuff, I also log the input parameter values that are sent to the SPROC.
/* ========================================================================================== */
/* Example: How to log SPROC execution */
/* ========================================================================================== */
CREATE TABLE DBActivityLog
(DBActivityID int IDENTITY NOT NULL
CONSTRAINT PK_DBActivityLog_On_DBActivityID PRIMARY KEY CLUSTERED,
DBASPROCvarchar(300)NULL,
DBAMessagevarchar(1000)NULL,
DBAAppNamevarchar(150)NULLDEFAULT APP_NAME(),
DBAHostvarchar(50)NULLDEFAULT HOST_NAME(),
DBAUservarchar(50)NULLDEFAULT USER,
DBADatedatetimeNULLDEFAULT GETDATE()
)
ON MyDatabase_data
GO
/* ========================================================================================== */
CREATE TABLE AppErrorLog
(EID int IDENTITY NOT NULL
CONSTRAINT PK_AppErrorLog_On_EID PRIMARY KEY CLUSTERED,
EDatedatetimeNOT NULL,
EUservarchar(50)NULL,
EMessagevarchar(1000)NULL,
ESource varchar(150)NOT NULL,
ENointNOT NULLDEFAULT 0,
ESeverityintNOT NULLDEFAULT 0,
ELineNo intNOT NULLDEFAULT 0,
EHost varchar(50)NULL
)
ON MyDatabase_data
GO
/* ========================================================================================== */
/* ========================================================================================== */
-- Example Stored Procedure using both tables created above
CREATE PROCEDURE dbo.xp_usp_Read_Account_Rec
@acctidint,
@spstatintOUTPUT,
@errmsgvarchar(200)OUTPUT,
@recnintOUTPUT
AS
DECLARE @numrecs int
DECLARE @pvalue varchar(1000) --log db activity
SET NOCOUNT ON
SET @spstat = 1 -- go ahead and set to ok
SET @errmsg = '' -- go ahead and set to ok
SET @recn = 0 -- go ahead and set to ok
BEGIN TRY
--log db activity
SET @pvalue = CONVERT(varchar(100),@acctid)
INSERT INTO DBActivityLog
VALUES ('dbo.xp_usp_Read_Account_Rec',CONVERT(varchar(1000),'SPROC call - Params= ' + @pvalue),APP_NAME(),HOST_NAME(),USER,GETDATE())
--Select the desired information
SELECT AcctID,CandidateID,CandidateStatusID,AcctOpen,AcctStatusID,AcctComment,CurrRec,LCHost,LCUser,LCDate
FROM Account
WHERE AcctID=@acctid
SET @numrecs = @@rowcount
if @numrecs=0
BEGIN
SET @spstat = -1
SET @errmsg = 'No record selected'
SET @recn = 0
END
RETURN @spstat
END TRY
BEGIN CATCH
DECLARE@ErrorNoint,
@Severityint,
@Stateint,
@LineNoint,
@errmessagevarchar(1000)
SELECT@ErrorNo = ERROR_NUMBER(),
@Severity = ERROR_SEVERITY(),
@State = ERROR_STATE(),
@LineNo = ERROR_LINE(),
@errmessage = ERROR_MESSAGE()
SET @errmsg = CONVERT(varchar(200), @errmessage)
SET @spstat = 0
INSERT INTO AppErrorLog
VALUES (GETDATE(), USER, @errmessage, 'dbo.xp_usp_Read_Account_Rec', @ErrorNo, @Severity, @LineNo, HOST_NAME())
END CATCH
GO