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
as
begin
insert into t_EmployeeInformation(employee_Name,employee_EmailID)
values(@employee_Name,@employee_EmailID)
set @employee_ID = SCOPE_IDENTITY()
end

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

Thanks
A Rahim Khan

Advertisements
  1. No trackbacks yet.

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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: