It is very useful and recommend specially for debugging stored procedures or keep track of every activity done within a system or website to use a SQL Log.
A user’s SQL Log is nothing more than a table you can create where you can insert different parts of a transaction with description and time, this will help you determine if something is not doing what is supposed to do even when the stored procedure is not erroring out.
The first thing to do is to create a table:
CREATE TABLE dbo.Activity_Log( id int PRIMARY KEY IDENTITY(1,1), sp_name varchar(200), vars varchar(200), description varchar(max), date_added datetime )
Then use this simple INSERT statement between your code whenever you want to track a transaction.
DECLARE @language varchar(100) SET @language = 'English' INSERT INTO Activity_Log (sp_name, vars, description, date_added) VALUES (object_name(@@procid), @language, '@language is set to English', getdate()) SET @language = 'Spanish' INSERT INTO Activity_Log (sp_name, vars, description, date_added) VALUES (object_name(@@procid), @language, '@language is set to Spanish', getdate())