In this tutorial of SQL Server, We will learn Stored Procedure in SQL Server. Stored Procedure is a set of Prepared SQL Statements that can be reused multiple times.
After completing this tutorial you will be able to understand:
- What is Stored Procedure IN SQL Server?
- Advantages of SQL Stored Procedure.
- How to create Stored Procedure in SQL Server?
Stored Procedure is a pre compiled SQL Code that is saved so that we reuse the code over and over again. Stored Procedure is a group of SQL Statements that are used to perform a specific task.
In addition writing the same code over and over again, we can also pass parameters to the Stored Procedure to get the required output.
The main benefit of using Stored Procedure is it increases your Database Performance.
The syntax for Creating Stored Procedure:
Create PROCEDURE procedure_Name AS BEGIN --SQL Statement End Go
Advantages of using the Stored Procedure:
1- Increase Performance:
The Stored Procedure increases the performance by reducing the amount of data sent to the database server. Stored Procedure becomes more useful when we have a server with less bandwidth.
2- Recompilation is not required:
The normal SQL Statement is compiled every time when it is sent to the server for execution whereas Stored Procedure compiled only once when it is created. It does not require recompilation before execution until it is modified.
3-Provides Code ReUSability:
As we have already discussed with Stored Procedure we don’t need to write a SQL Code over again and again. We create a Stored Procedure once and it can be used by multiple clients on the server for multiple times.
4- Store Business Logic:
We can use a Stored Procedure to store Business Logic of the component. It is secure to Store Business Logic in a Stored Procedure. If we need to change our business logic then we may only need to change the logic inside the Stored Procedure not in the files contained on the Web Server.
Example OF Stored Procedure in SQL Server:
Create the following Database and Table.
CREATE DATABASE SPDemo
CREATE TABLE [dbo].[Employee] ( ID INT PRIMARY KEY, FirstName VARCHAR(50), LastName VARCHAR(50), Salary FLOAT )
Stored Procedure for Inserting Record in Employee Table:
CREATE PROCEDURE [dbo].[uspAddEmployee] @pID INT, @pFirstName VARCHAR(50), @pLastName VARCHAR(50), @pSalary FLOAT AS BEGIN SET NOCOUNT ON; INSERT INTO [dbo].[Employee](ID,FirstName,LastName,Salary) VALUES(@pID,@pFirstName,@pLastName,@pSalary) SET NOCOUNT OFF; END
EXEC [dbo].[uspAddEmployee] 1,'Anil','Singh',20000
Stored Procedure for Reading Records in Employee Table:
CREATE PROCEDURE [dbo].[uspGetEmployeeRecords] AS BEGIN SET NOCOUNT ON; SELECT ID,FirstName,LastName,Salary FROM [dbo].[Employee] SET NOCOUNT OFF; END
Stored Procedure of Updating Records in Employee Table:
CREATE PROCEDURE [dbo].[uspUpdateEmployee] @pID INT, @pFirstName VARCHAR(50), @pLastName VARCHAR(50), @pSalary FLOAT AS BEGIN SET NOCOUNT ON; UPDATE [dbo].[Employee] SET FirstName=@pFirstName,LastName=@pLastName,Salary=@pSalary WHERE ID=@pID SET NOCOUNT OFF; END
EXEC [dbo].[uspUpdateEmployee] 1,'Anil','Gupta',40000
Stored Procedure of Deleting Records in Employee Table:
CREATE PROCEDURE [dbo].[uspDeleteEmployee] @pID INT AS BEGIN SET NOCOUNT ON; DELETE FROM [dbo].[Employee] WHERE ID=@pID SET NOCOUNT OFF; END
EXEC [dbo].[uspDeleteEmployee] 1
You can download the script for this tutorial from here.
Hope you understand this important tutorial of SQL Server. I would like to have your Suggestions, Feedback and comments.