Stored Procedure For Beginners

In this blog you will learn how to write Store Procedure in SQL Server.

What is Stored Procedure

Stored procedure is a name collection of SQL statements and procedural logic,  i.e., compiled, verified, and stored in the server database. A stored procedure is typically treated like other database object and controlled through server security mechanism.

Syntax for Stored Procedure:

Create Procedure Employee_proc
(
//all parameters will be written here
)
AS
BEGIN
//Statements are here
END

Now, Create an Employee table:

Create Table Employee
(
   EmpId INT identity(1,1) NOT NULL Primary Key,
   EmpName varchar(50),
   EmpDesignation varchar(50),
   EmpAddress varchar(50)
)

Output of the above query:



After Creation of Table, now it's time to create procedure for Update, Insert, and Delete.

Create Procedure Employee_proc
(
   @EmpId INT,
   @EmpName varchar(50),
   @EmpDesignation varchar(50),
   @EmpAddress varchar(50),
   @Opt varchar(8)
)
as
Begin
   if(@Opt='Insert' )
Begin
   Insert into Employee( EmpName,EmpDesignation,EmpAddress)
   Values(@EmpName,@EmpDesignation,@EmpAddress)
End
   if(@Opt='Update')
Begin
Update Employee set EmpName=@EmpName ,EmpDesignation=@EmpDesignation,
   EmpAddress=@EmpAddress Where EmpId=@EmpId
End
   if(@Opt='Delete')
Begin
   Delete from Employee Where EmpId=@EmpId
End
End

Now, output  the above query:



Now execute the store procedure:

Procedure_Name follow by parameters we pass,

For example:

 

Employee_proc o,'Amit','Developer','Mumbai','Insert'

Select * from Employee

Note:

 

1. flag(@Opt) we are using for calling queries in Dataset Method.
2. It makes our task easier for calling queries.

Advantages of Using Stored Procedure

Execution Plan Retention and Reuse (SP are compiled and their execution plan is cached and used again to when the same SP is executed again).

So we have successfully created our first stored procedure. I hope this article is useful for all readers.

If you have a suggestion then please contact me.