How to Write Stored Procedure in SQL Server 2005

I always prefer writing stored procedure over writing query to insert data in table. If you have an auto-increment field in table as key, you will require this key value of last inserted data for various purposes. Writing stored procedure is the simplest way to address this scenario.

//Create Table
create table t_EmployeeInformation
employee_ID int identity(1,1) NOT NULL,
employee_Name varchar(50),
employee_EmailID varchar(20)

//Create Procedure
create procedure p_InsertEmployee
@employee_Name varchar(50),
@employee_EmailID varchar(40),
@employee_ID int output
insert into t_EmployeeInformation(employee_Name,employee_EmailID)
set @employee_ID = SCOPE_IDENTITY()

//Use Procedure to Insert Data
declare @employee_ID int
exec p_InsertEmployee
'khan', '',@employee_ID out
select @employee_ID

A Rahim Khan

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: