Post
Ask Question

Stored Procedures in SQL Server

Introduction

 
In this blog, I will explain the SQL Store Procedure. A stored procedure is a group of SQL statements that have been previously created and stored in the server database. It creates SQL queries to be stored and executed on the server. Parameters can also be passed to a stored procedure so that the stored procedure can act based on the parameter value(s) passed. Stored procedures accept input parameters so that a single procedure can be used over the network by several clients using different input data. One advantage of this procedure is that it helps reduce network traffic and improve performance.
 

Syntax

 
  1. CREATE PROCEDURE MASTER_PROC  
  2. AS  
  3. BEGIN  
  4. SELECT * FROM EMPLOYEE  
  5. END  
  6. EXEC MASTER_PROC  

 

Create a Store Procedure
 
1. The “CREATE PROCEDURE” statement is used to create a new stored procedure in a database.
2. Declare the Parameters and data types.
3. I have used the IF-ELSE Condition to perform all operations in a single Stored Procedure (SP).
4. “EXEC” keyword is used to execute the stored procedure

Coding

 
  1. CREATE PROCEDURE MASTER_PROC (@ID INT,@FIRST_NAME NVARCHAR(100),@LAST_NAME NVARCHAR(100),@DATE NVARCHAR(100),@TRANSACTTYPE VARCHAR(1))  
  2. AS  
  3. BEGIN  
  4. IF(@TRANSACTTYPE='I')  
  5. BEGIN  
  6. INSERT INTO MASTER_TABLE(FIRST_NAME,LAST_NAME,DATE) VALUES(@FIRST_NAME,@LAST_NAME,@DATE)  
  7. END  
  8. ELSE IF(@TRANSACTTYPE='U')  
  9. BEGIN  
  10. UPDATE MASTER_TABLE SET FIRST_NAME=@FIRST_NAME,LAST_NAME=@LAST_NAME,DATE=@DATE WHERE ID=@ID  
  11. END  
  12. ELSE IF(@TRANSACTTYPE='D')  
  13. BEGIN  
  14. DELETE FROM MASTER_TABLE WHERE ID=@ID  
  15. END  
  16. END  

Advantages:

 
1. Stored procedures are used so multiple queries execute a single execution.
2. They allow faster execution.
3. Help reduce network traffic
4. Stored procedures can be used for input and output parameters.
Next Recommended Reading Efficient Paging Using Stored Procedure In SQL Server