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.
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:
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:
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:
