Stored Procedure in SQL Server 2012

Introduction

 
In this article, I give a short description of Stored Procedures. In this article I describe how to create a Stored Procedure, use of Stored Procedures, the benefits of Stored Procedures and Stored Procedures for insert, update, delete and join.
 

Stored Procedure

 
Stored Procedures are a precompiled set of one or more statements that are stored together in the database. They reduce the network load because of the precompilation. We can create a Stored Procedure using the "Create proc" statement.
 

Why we use Stored Procedure

 
There are several reasons to use a Stored Procedure. They are a network load reducer and decrease execution time because they are precompiled. The most important use of a Stored Procedure is for security purposes. They can restrict SQL Injection. We can avoid SQL injection by use of a Stored Procedure.
 

Difference Between Stored Procedure and Function

  1. A Stored Procedure may or may not return a value but a function always returns a value.
  2. We can use a function in a Stored Procedure but we can't use a Stored Procedure in a Function
Here we create two tables named emp and emp1 on which we apply a Stored Procedure.

Creation of emp table
  1. create table emp(empId int, empName varchar(15), empAge int )  
Inserting values
  1. insert into emp  
  2. select 1,'deepak',21 union all  
  3. select 2,'Arora',21 union all  
  4. select 3,'Vandna',22  
Output
  1. select * from emp 
storeprocedue-in-sql-emp.jpg
 
Creation of emp1 table
  1. create table emp1(empId int,empAdd varchar(35))   
Inserting the value
  1. insert into emp1  
  2. select 1,'delhi'union all  
  3. select 2,'banglore'union all  
  4. select 3,'usa'  
Output
  1. select * from emp1
store-procedure-in-sql-emp1.jpg
 

Type of Stored Procedure

  1. System Stored Procedure
  2. User Defined Stored Procedure
Here I describe only User Defined Stored Procedure
 

Types of User Defined Store Procedure

  1. Non-parameterized Stored Procedure
  2. Parameterized Stored Procedure

Non-parameterized Stored Procedure

 
This type of Stored Procedure does not accept parameters.
 

Non-parameterized Stored Procedure for Showing All Records

  1. create proc usp_select  
  2. as  
  3. select * from emp   
Output
  1. exec usp_select 
store-procedure-in-sql-usp-select.jpg
 

Non-parameterized Stored Procedure for Join

  1. create proc usp_join  
  2. as  
  3. select e.empId,e.empName,e.empAge,e1.empAdd from emp e inner join emp1 e1 on e.empId=e1.empId  
Output
  1. exec usp_join
store-procedure-in-sql-usp-join.jpg
 

Parameterized Stored Procedure

 
These accept one or more Parameters. It is important that the data type of the parameters be the same as the data type in the tables.
 

Parameterized Stored Procedure for Insert

  1. create proc usp_insert(@id int,@name  varchar(15),@age int)  
  2. as  
  3. insert into emp values(@id,@name,@age)  
Output
  1. exec usp_insert 4,'Ankit',19   
  2.   
  3. exec usp_select
store-procedure-in-sql-usp-insert.jpg
 

Parameterized Stored Procedure for Update

  1. create proc usp_update(@id int,@name  varchar(15),@age int)  
  2. as  
  3. update emp set empName=@name,empAge=@age where empId=@id  
Output
  1. exec usp_update 4,'Mohan',20  
  2.   
  3. exec usp_select
store-procedure-in-sql-usp_update.jpg
 

Parameterized Store Procedure for delete

  1. create proc usp_delete(@id int)  
  2. as  
  3. delete from emp where empId=@id  
Output
  1. exec usp_delete 4  
  2.   
  3. exec usp_select
store-procedure-in-sql-usp-delete.jpg
 

Summary

 
In this article, I described Store Procedures in SQL Server. I hope this article has helped you to understand this topic. Please share if you know more about this. Your feedback and constructive contributions are welcome.