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?

Introduction:

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.

Stored Procedure

The syntax for Creating Stored Procedure:

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
USE SPDemo

Stored Procedure for Inserting Record in Employee Table:

Testing uspAddEmployee:

Stored Procedure for Reading Records in Employee Table:

Testing uspGetEmployeeRecords:

Stored Procedure of Updating Records in Employee Table:

Testing uspUpdateEmployee:

Stored Procedure of Deleting Records in Employee Table:

Testing uspUpdateEmployee:

You can download the script for this tutorial from here.

Conclusion:

Hope you understand this important tutorial of SQL Server. I would like to have your Suggestions, Feedback and comments.

View More:

Thank You.