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:

  1. Create Table Employee
  2. (
  3. EmpId INT identity(1,1) NOT NULL Primary Key,
  4. EmpName varchar(50),
  5. EmpDesignation varchar(50),
  6. EmpAddress varchar(50)
  7. )
Output of the above query:

see output

After Creation of Table, now it's time to create procedure for Update, Insert, and Delete.
  1. Create Procedure Employee_proc
  2. (
  3. @EmpId INT,
  4. @EmpName varchar(50),
  5. @EmpDesignation varchar(50),
  6. @EmpAddress varchar(50),
  7. @Opt varchar(8)
  8. )
  9. as
  10. Begin
  11. if(@Opt='Insert' )
  12. Begin
  13. Insert into Employee( EmpName,EmpDesignation,EmpAddress)
  14. Values(@EmpName,@EmpDesignation,@EmpAddress)
  15. End
  16. if(@Opt='Update')
  17. Begin
  18. Update Employee set EmpName=@EmpName ,EmpDesignation=@EmpDesignation,
  19. EmpAddress=@EmpAddress Where EmpId=@EmpId
  20. End
  21. if(@Opt='Delete')
  22. Begin
  23. Delete from Employee Where EmpId=@EmpId
  24. End
  25. End
Now, output the above query:

Output of the query

Now execute the store procedure:

Procedure_Name follow by parameters we pass,

For example:

  1. Employee_proc o,'Amit','Developer','Mumbai','Insert'
output
  1. Select * from Employee
Run Query

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.