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())
This is of course a very basic example, but it shows the purpose of having a user’s log within a stored procedure.
This method can also be used to track all user activity within a website, like if a user adds something to the shopping cart, if he deletes it, if he orders or cancels a products, all their activity could be logged for security reasons.