Sometimes there is a need to insert,
select, update and delete records from a table using a single Stored Procedure
instead of creating separate Stored Procedures for each operation.
Suppose I have one .aspx web page in
which I need a to insert, select, update and
delete records. To do that instead of creating four Stored
Procedures to perform these tasks I will create a single Stored Procedure
to satisfy my requirements and I will access it in code behind
according to the action performed by the end user on a button click.
I have written this article
specially focusing on newcomers and anyone new to SQL Stored Procedures so
let us start with a basic introduction.
What is Stored Procedure?
A Stored Procedure is a group 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 database.
A Stored Procedure is a group 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 database.
Creating a Stored Procedure
Before creating a Stored Procedure,
we will create one table named employee in the SQL database which looks as in
the following image.
I have set the primary key on
the id column for the Identy specification.
Now we have a table to perform these operations. Now let us start to create the Stored Procedure.
The Stored Procedure is created
using the keyword Create Procedure followed by the procedure name. Let us
create the Stored Prcedure named EmpEntry as given below.
create Procedure EmpEntry
(
--variable declareations
@Action Varchar (10), --to perform operation according to string passed to this varible such as Insert,update,delete,select
@id int=null, --id to perform specific task
@Fname Varchar (50)=null, -- for FirstName
@MName Varchar (50)=null, -- for MName
@Lname Varchar (50)=null -- for LastName
(
--variable declareations
@Action Varchar (10), --to perform operation according to string passed to this varible such as Insert,update,delete,select
@id int=null, --id to perform specific task
@Fname Varchar (50)=null, -- for FirstName
@MName Varchar (50)=null, -- for MName
@Lname Varchar (50)=null -- for LastName
)
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---exec EmpEntry @Action='delete' ,@Fname='S',@MName='R',@Lname='M',@id='13' --added by vithal wadje on 18-10-2012 for Csharp contribution
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
as
Begin
SET NOCOUNT ON;
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---exec EmpEntry @Action='delete' ,@Fname='S',@MName='R',@Lname='M',@id='13' --added by vithal wadje on 18-10-2012 for Csharp contribution
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
as
Begin
SET NOCOUNT ON;
If @Action='Insert' --used to insert records
Begin
Insert Into employee (FirstName,MName,LastName)values(@Fname,@MName,@Lname)
End
Begin
Insert Into employee (FirstName,MName,LastName)values(@Fname,@MName,@Lname)
End
else if @Action='Select' --used to Select records
Begin
select *from employee
end
else if @Action='Update' --used to update records
Begin
update employee set FirstName=@Fname,MName=@MName,LastName=@Lname where id=@id
End
Else If @Action='delete' --used to delete records
Begin
delete from employee where id=@id
end
End
Begin
select *from employee
end
else if @Action='Update' --used to update records
Begin
update employee set FirstName=@Fname,MName=@MName,LastName=@Lname where id=@id
End
Else If @Action='delete' --used to delete records
Begin
delete from employee where id=@id
end
End
In the above Stored Procedure
throught comments I have clearly explained which block is used for which
purpose, so I have briefly explained it
again. I have used @Action variable and assigned the string to them and
according to the parameter passed to the Stored Procedure the particular block
will be executed because I have kept these blocks or conditions in
nested If else if conditional statements.
"The most important thing
is that I have assigned null to each variable to avoid the effect on the
parameter passed to the Stored Procedure because we are passing a
different number of parameters but not the same number of parameters to the
Stored Procedure to perform these tasks."
After creating this Stored
Procedure, now let us use it.
To execute the Stored
Procedure EmpEntry that we created we need to use the keyword exec followed by the procedure
name and the parameter list. I
have explained how to use it below.
Inserting the Records into the
Employee table that we created with the EmpEntry procedure; see:
exec EmpEntry @Action='Insert'
,@Fname='vithal',@MName='G',@Lname='Wadje'
After running this query the records
will be inserted into the table employee. To see the records inserted into the
table the run following query:
select * from employee
the output will be as shown in the following:
the output will be as shown in the following:
Their are two records you have seen because I have executed the procedure two times.
- Selecting Records From table
exec EmpEntry @Action='Select'
The output will be as follows:
- Updating Records of table
exec EmpEntry @Action='Update'
,@Fname='Manish',@MName='Kapil',@Lname='Sawant',@id=2
After executing the above query the
id number 2 record will be updated in the table.
To see, run the query: select * from employee
The output will be as shown in the
following:
- Deleting the Records from table
exec EmpEntry @Action='delete'
,@id=2
After executing the above query the
id number 2 record will be deleted from the table.
To see, run the query: select * from employee
The output will be as shown in the
following:
