Audit Trail is one of the task client asked for in most of the project so that they can keep watch on crucial fields regarding its change.

I have seen many small & few medium sized companies is still not hiring SQL Server professional and used to go ahead with .NET developer. .NET developer generally keep their knowledge updated with .NET technology but they lack of knowledge in SQL Server domain so for Audit Trail, they still rely on old pseudo table (INSERTED, DELETED) accessible in Trigger. But in SQL Server 2005 and later, you don’t even need to rely on Triggers for Audit Trail but you can use new “OUTPUT” clause.

“OUTPUT” clause is still much underutilized feature even after 7 years. That is the reason I am writing on Audit trail and OUTPUT clause once again.

Within “OUTPUT” clause, you can access pseudo table and generate your “Audit Trail” log. I will show you with an example.

We will have one table of Test comes to an environmental company to analyze, if client change the Test s/he wants to perform, we have to keep trail on that, if he cancel any test, we have to keep trail on that too.

--we will keep "Audit Trail" for below given table

create table ClientOrder

(

      OrderID varchar(5)

      ,ClientID varchar(5)

      ,Test varchar(20)

      ,OrderDate datetime default getdate()

)

GO

--following is the table, we will keep "Audit Trail" in.

--this will keep track of all data changed

CREATE TABLE AuditOfOrder

(

      Id INT Identity(1,1)

      ,OrderID varchar(50)

      ,OldTest varchar(20)

      ,NewTest varchar(20)

      ,DMLPerformed varchar(15)

      ,ChangeDate datetime default getdate()

)

GO

--inserting data in "ClientOrder" table

--all insert will be stored in audit trail table too via "OUTPUT" clause

INSERT INTO ClientOrder(OrderID,ClientID,Test)

Output Inserted.OrderID,Inserted.Test,Inserted.Test,'Insert' into AuditOfOrder(OrderID,OldTest,NewTest,DMLPerformed)

VALUES('A1001','CHEM1','VOCMS Group1')

GO

--inserting data in "ClientOrder" table

--all insert will be stored in audit trail table too via "OUTPUT" clause

INSERT INTO ClientOrder(OrderID,ClientID,Test)

Output Inserted.OrderID,Inserted.Test,Inserted.Test,'Insert' into AuditOfOrder(OrderID,OldTest,NewTest,DMLPerformed)

VALUES('A1001','CHEM1','Pesticide Group1')

GO

--let us see what we have in both tables now.

select * from ClientOrder

select * from AuditOfOrder

go

--we will perform UPDATE on "ClientOrder" table

--which will be recorded in "AuditOfOrder" table too

INSERT INTO AuditOfOrder(OrderID,OldTest,NewTest,DMLPerformed)

SELECT

t.OrderID,

t.OldTest,

t.NewTest,

t.DMLPerformed

FROM

(

UPDATE ClientOrder

      SET Test ='SVOC Stars'

OUTPUT

      inserted.OrderID AS OrderID,

      deleted.Test AS OldTest,

      inserted.Test AS NewTest,

      'UPDATE' as DMLPerformed

WHERE ClientOrder.Test='VOCMS Group1'

) t

GO

--let us see what we have in both tables now.

select * from ClientOrder

select * from AuditOfOrder

go

--Finally the log of Delete will be stored in the same way.

INSERT INTO AuditOfOrder(OrderID,OldTest,NewTest,DMLPerformed)

SELECT

t.OrderID,

t.OldTest,

t.NewTest,

t.DMLPerformed

FROM

(

DELETE FROM ClientOrder

OUTPUT

      deleted.OrderID AS OrderID,

      deleted.Test AS OldTest,

      deleted.Test AS NewTest,

      'DELETE' as DMLPerformed

WHERE ClientOrder.Test='SVOC Stars'

) t

GO

--let us see what we have in both tables now.

select * from ClientOrder

select * from AuditOfOrder

go

Isn’t this interesting? Start using it.

BTW, I have previously written some articles regarding Audit Trail techniques (old & new both), if you want to refer it, have a look at below links:

http://www.sqlhub.com/2009/11/audit-in-sql-server-2008.html

http://www.sqlhub.com/2009/03/auditing-trail-with-trigger-in-sql.html

http://www.sqlhub.com/2009/03/output-deleted-and-inserted-from-insert.html

http://www.sqlhub.com/2009/08/output-clause-in-sql-server-20082005.html

Reference: Ritesh Shah

http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

Ask me any SQL Server related question at my “ASK Profile”

Today I was working on one Audit Trail project and thought to test OUTPUT clause. As soon as I wrote OUTPUT clause, it pops up in my mind that I have not written anything about OUTPUT clause in my blog so far. So, I felt, this is the time to write something about this.

You people might be aware with pseudo table (INSERTED and DELETED) in trigger, this is something similar concept, the difference is, you can get pseudo table inside trigger and you can use OUTPUT clause outside trigger too.

Let us see its usefulness by one small example.

--create table for demo and insert few records

create table testOutPut(tid int identity(1,1), name varchar(50))

insert into testOutPut (name)

select 'Ritesh' union all

select 'Rajan' union all

select 'Pinal' union all

select 'Bihag' union all

select 'John' union all

select 'Bhaumik' union all

select 'Avi' union all

select 'James'

go

--check whether all records came with proper tid

select * from testOutPut

go

--create temp duplicate table which will store data which are deleted

create table #deleted (id int, name varchar(50))

--deleting records from testOutPut table and inserting those deleted

--records into temp table with help of OUTPUT clause and DELETED pseudo table

delete testOutPut

output DELETED.* into #deleted

where tid < 3

--checking temp table

select * from #deleted

--this will show deleted data on screen

--but won't store anywhere like we did in previous snippet

--and stored data in #deleted (temp table)

delete testOutPut

output DELETED.*

where tid >3

select * from testOutPut

go

select * from #deleted

go

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

Herewith, I am giving you one more example of OUTPUT parameter in Stored Procedure for calculating BSA (Body Surface Area). You are in development of medical software than BSA is not a new term for you. If your body surface are is between 1 to 2 than it is normal as per my little knowledge about BSA. I am giving a sample example which will calculate BSA based on the given height and weight. Height and Weight should be either in kg (weight) and cm (height) or in lbs (weight) and inch (height). You can make it more customize by giving more dynamic conversions.

--CREATING stored procedure to return BSA (Body Surface Area)

--The calculation is from the formula of DuBois and DuBois:

--BSA = (W 0.425 x H 0.725) x 0.007184

--where the weight is in kilograms and the height is in centimeters.

--

--

--DuBois D, DuBois EF. A formula to estimate the approximate surface area

--if height and weight be known. Arch Intern Medicine. 1916; 17:863-71.

--Wang Y, Moss J, Thisted R. Predictors of body surface area.

CREATE PROC CalcBSA

@option INT,

@weight FLOAT,

@height FLOAT,

@bsa FLOAT OUTPUT

AS

SET NOCOUNT ON

--if weight and height are in kg and cm accordingly

IF @option=1

BEGIN

SET @bsa=power(@weight,0.425)*power(@height,0.725)*0.007184

END

--if weight and height are in lbs and inch accordigly

ELSE

BEGIN

SET @weight=(@weight/2.2046)

SET @height=@height*2.54

SET @bsa=power(@weight,0.425)*power(@height,0.725)*0.007184

END

GO

--once you done with creating stored procedure, let us see whether actually it works!!!!

DECLARE @BSA FLOAT

EXECUTE calcbsa 1,84,180,@BSA OUTPUT

PRINT @BSA

GO

If you are new to stored procedure and wants to study it than do have a look at my following basic articles. Those articles contain from basic definition of stored procedure to different usage of SP.

http://www.sqlhub.com/2009/03/stored-procedure-in-microsoft-sql.html

http://www.sqlhub.com/2009/03/return-data-with-output-parameter-from.html

http://www.sqlhub.com/2009/03/dml-insert-with-multiple-ways-in-sql.html

http://www.sqlhub.com/2009/03/delete-many-multiple-records-in-bunch.html

http://www.sqlhub.com/2009/03/startup-stored-procedure-sql-server.html

http://www.sqlhub.com/2009/03/create-your-own-system-stored-procedure.html

Reference: Ritesh Shah
http://www.SQLHub.Com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

Herewith, I am keeping my promise and moving ahead with topic of stored procedure. I will be explaining how to return data from stored procedure in SQL Server with OUTPUT parameter. If you are new to Stored Procedure than I kindly advice you to move to my prior article about stored procedure at:

http://www.sqlhub.com/2009/03/stored-procedure-in-microsoft-sql.html

When you have need to return data to the calling procedure, you should use of OUTPUT parameter of SQL Server in Stored Procedure.

If you are returning record set for single value, I strongly insist to use OUTPUT parameter as it is much much faster than returning the value.

--create one table for demo

use adventureworks

CREATE TABLE emps

(

Name VARCHAR(50),

Dept VARCHAR(10),

Company VARCHAR(15)

)

--INSERT records

INSERT INTO emps

SELECT 'Ritesh','MIS','echem' UNION ALL

SELECT 'Bihag', 'MIS', 'CT' UNION ALL

SELECT 'Rajan', 'account','Marwadi' UNION ALL

SELECT 'Alka','account','tata' UNION ALL

SELECT 'Alpesh','Chemical','echem'

GO

--creating stored procedure which will return data with OUTPUT parameter

CREATE PROC getEmpDeptbyEmpName(@EmpName VARCHAR(50),@EmpDept VARCHAR(10) OUTPUT)

AS

SELECT @EmpDept=dept FROM emps WHERE Name=@EmpName

GO

--calling SP and catching return value in @EmpDept

DECLARE @EmpDept VARCHAR(50)

EXECUTE getEmpDeptbyEmpName 'Ritesh', @EmpDept OUTPUT

SELECT @EmpDept AS 'Department'

GO

Reference: Ritesh Shah
http://WWW.SQLHub.Com
Note: Microsoft Book online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com