Add and Update Records in single Stored Procedure using SQL Server 2008

In this article I describe how to Add and Update Table Records in single Stored Procedure.
  • 2471

Introduction

Sometimes we need to add and update records from a table through a single Stored Procedure rather creating three Stored Procedures to add and update records. So in this article I describe how to create a single Stored Procedure to satisfy my requirements.

Stored Procedure

Stored procedure is a set of logical SQL statements to perform a specific task such as insert, select, update and delete operations on a table and so on which is stored in a SQL Server database. We don't need to compile again these stored procedure at using time. It makes stored procedure faster than execution of normal SQL statement.

First we create a table named emp to apply a stored procedure on this table:

Creation of table

create table emp(emp_id int,emp_name varchar(10))

go

insert into emp

select 1,'d' union all

select 2,'e' union all

select 3,'f' union all

select 4,'g' union all

select 5,'h' union all

select 6,'i'

go

select * from emp

 Output:

Table-in-sql.jpg

Creation of Stored Procedure

We use CREATE PROC statement for creating a store procedure followed by procedure name. Now create a stored procedure named sp_EmpDetails as follows:

create procedure sp_EmpDetails

(@type varchar(10),

@emp_id int ,

@emp_name varchar(10))

as

begin

set nocount on;

if @type='Insert'

begin

insert into emp(emp_id,emp_name) values (@emp_id,@emp_name)

end

else if @type='Update'

begin

update emp set emp_name=@emp_name where emp_id=@emp_id

end

end

Press F5 and Run the above code.

Inserting the Records into the emp table using stored procedure:

exec sp_EmpDetails

@type='Insert',

@emp_id=7,

@emp_name='abc'

Run above code and select emp table:

select * from emp

Output:

insert_with_sp-in-sql.jpg

Updating the Records into the emp table using stored procedure:

exec sp_EmpDetails

@type='Update',

@emp_name='Manish',

@emp_id=2

Run above code and select emp table:

select * from emp

Output:

update_with_sp-in-sql.jpg

© 2013 dotNetheaven. All rights reserved.