CODEWITHSUNDEEP
sql-serverloggingstored-procedures
Hưng Nguyễn Văn
Hưng Nguyễn Văn

Reputation: 23

SQL Server - Write log to a table when executing stored procedure

I want to insert a log record to a table when i execute a stored procedure.

Firstly,

I created a table with 3 column named:

ID(AUTO INCREMENT),MESSAGE(VARCHAR), CREATED_ON(DATETIME)

Secondly I wrote a procedure:

 create  PROCEDURE        DO_LOG(@MSG_IN VARCHAR) 
 as
 BEGIN

 INSERT INTO test.dbo.TB_ETLLOG(MESSAGE,CREATED_ON) 
 VALUES(@MSG_IN,GETDATE());
 END;

I want to write log when executing another procedure so i use this:

EXEC DO_LOG @MSG_IN ='Start DO_ACCOUNT_AML'

But it doesn't write log to table. Is there any mistake in my code? Thanks.

Upvotes: 2

Views: 4932

Answers (1)

live-love
live-love

Reputation: 52366

Here is how you can create a logging stored procedure that you can add to your code:

Create table:

    CREATE TABLE [dbo].[Log](
        [LogID] [int] IDENTITY(1,1) NOT NULL,
        [LogTime] [datetime] NOT NULL,
        [LogAction] [varchar](50) NOT NULL,
        [LogNote] [varchar](max) NOT NULL,
     CONSTRAINT [PK_CourionLog] PRIMARY KEY CLUSTERED 
    (
        [LogID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
    GO

Create Stored Procedure:

CREATE PROCEDURE [dbo].[AddLog] 
        @LogAction varchar(50), 
        @LogNote varchar(max), 
        @LogTime datetime = null

    AS
    BEGIN
        -- SET NOCOUNT ON added to prevent extra result sets from
        -- interfering with SELECT statements.
        SET NOCOUNT ON;

            if @LogTime is null
                set @LogTime = getdate();

            INSERT INTO Log(LogTime, LogAction, LogNote)
                Select @Logtime, @LogAction, @LogNote 

    END

Usage:

DECLARE @msg VARCHAR(1000);
DECLARE @tmpCount INT;
SET @tmpCount = 1
SET @msg = 'Completed LastName Check: ' + cast(@tmpCount as varchar(10))
EXECUTE [dbo].[AddLog] 'INFO', @msg

EXECUTE [dbo].[AddLog] 'ERROR', 'This is an error message.'

See log messages:

select * from [dbo].[Log]

Upvotes: 1

Related Questions