Stored Procedure For Beginners

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:

see output

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:

Output of the 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:

flag(@Opt) we are using for calling queries in Dataset Method.
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.

7 Comments

Join the conversation! Your thoughts help the community grow.

Shovon Pramanik5y ago
Okay, good one and specific....!!
Naveen Kumar7y ago
Employee_proc o,'Amit','Developer','Mumbai','Insert'
Subash10y ago
Nice Work Sir
Amit Kumar Singh10y ago
Thanku Sir
Santhakumar Munuswamy10y ago
Thanks for nice share
Amit Kumar Singh10y ago
Thanks Kashif
Kashif Sohail10y ago
Nice article