OUTPUT clause in SQL Server 2005

April 19, 2006

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