OUTPUT clause in SQL Server 2005

A SQL Server 2005 feature that I appreciate and want to demonstrate
is the OUTPUT clause. The OUTPUT clause helps in returning the actual data from
a table soon after an insert. Applications require return values such as
the new identity value, the computed column value or the default value generated
by the default constraint.

This article demonstrates how to return all of the
calculated column values and default values of a table, soon after an insert.

Let us assume that we have a table with computed columns
and default constraints as shown below.


USE [master]
Go
IF EXISTS (SELECT name FROM sys.databases
WHERE name = N’MyDatabase’)
DROP DATABASE [MyDatabase]
Go
Create database MyDatabase
Go
USE [MyDatabase]
GO
/****** Object: Table [dbo].[Employee]
Script Date: 04/09/2006 22:42:20 ******/
IF EXISTS (SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N’[dbo].[Employee]’)
AND type in (N’U’))
DROP TABLE [dbo].[Employee]
go
Create table Employee
(
EmpID int identity(1, 1)
constraint Employee_pk primary key,
FirstName varchar(100),
LastName Varchar(100),
Sex char(1),
Salutation as case
when Sex =’M’ then ‘Mr.’
when Sex =’F’ then ‘Ms.’
else ” end,
Salary Money,
Deductions as case
When Salary <3000 then .20 * Salary
when Salary between 30000
and 50000 then .26*Salary
when Salary between 50001
and 75000 then .30*Salary
else .35*Salary end,
DOJ datetime constraint DOJ_DEF
default getdate())

In this table, notice that Salutation is calculated based
on the value in the column Sex. The value in the Empid column is auto generated
by SQL Server. ‘Deductions’ is calculated based on the value in the Salary
column. The Date of Joining (DOJ) column is calculated using the current date
and time from the system clock.

Let us insert a few rows to this table, as shown below.


USE [MyDatabase]
GO
insert into Employee
(FirstName, LastName, Salary, sex)
select ‘Samuel’, ‘Adams’, 2000, ‘M’
insert into Employee
(FirstName, LastName, Salary)
select ‘Adam’, ‘Smith’, 45000
insert into Employee
(FirstName, LastName, Salary, sex)
select ‘Mizuho’, ‘Fukushima’, 22000, ‘F’
insert into Employee
(FirstName, LastName, Salary)
select ‘Minika’, ‘Shi’, 32000
insert into Employee
(FirstName, LastName, Salary, sex)
select ‘Jen’, ‘Lee’, 52000, ‘F’
insert into Employee
(FirstName, LastName, Salary)
select ‘Michael’, ‘Johnson’, 145000

Let us query the data from the employee table as shown
below.


USE [MyDatabase]
GO
select * from Employee

The results are shown below


EmpID, FirstName, LastName, Sex, Salutation, Salary, Deductions, DOJ
1, Samuel, Adams, M, Mr., 2000.00, 400.000000, 2006-04-09 23:05:31.863
2, Adam, Smith, NULL, , 45000.00, 11700.000000, 2006-04-09 23:05:31.933
3, Mizuho, Fukushima, F, Ms., 22000.00, 7700.000000, 2006-04-09 23:05:31.933
4, Minika, Shi, NULL, , 32000.00, 8320.000000, 2006-04-09 23:05:31.933
5, Jen, Lee, F, Ms., 52000.00, 15600.000000, 2006-04-09 23:05:31.933
6, Michael, Johnson, NULL, , 145000.00, 50750.000000, 2006-04-09 23:05:31.943

From the results, you can see the Deductions and
Salutations columns are calculated automatically and the DOJ has the default
value of the current date and time.

Usually, the application executes a procedure to insert
values to the table and it expects return values, like identity value.

Let us create a stored procedure, which returns a record
instead of just the identity value.


USE [MyDatabase]
GO
/****** Object: StoredProcedure [dbo].[employee_insert] Script Date: 04/09/2006 23:13:13 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N’[dbo].[employee_insert]’) AND type in (N’P’, N’PC’))
DROP PROCEDURE [dbo].[employee_insert]
go
Create procedure employee_insert
@Fname varchar(100),
@Lname varchar(100),
@sex char(1),
@Salary Money
as
declare @empoutput table (EmpID int ,FirstName varchar(100),
LastName Varchar(100),Sex char(1), Salutation char(3),Salary Money,
Deductions Money,DOJ datetime )
insert into Employee(FirstName,LastName,Salary,sex)
OUTPUT inserted.* into @empoutput
values(@Fname ,@Lname ,@Salary,@sex )
Select * from @empoutput

In this stored procedure, notice that the OUTPUT clause
generates and stores the output directly to the table variable.

Let us insert a value to this employee table using this
stored procedure.


USE [MyDatabase]
GO
exec employee_insert ‘Miho’,’Yoshikawa’,’F’,146000

When this procedure is executed, you see the following
result.


EmpID,FirstName,LastName,Sex,Salutation,Salary,Deductions,DOJ
7,Miho,Yoshikawa,F,Ms.,146000.00,51100.00,2006-04-09 23:16:44.920

Conclusion

This article has demonstrated the usage of the new SQL
Server 2005 clause, OUTPUT.

»


See All Articles by Columnist
MAK

Latest Articles