Understanding Stored Procedures in SQL: A Complete Guide

What is a Stored Procedure?

A stored procedure is a prepared SQL code that you can save, so the code can be reused over and over again.

So if you have an SQL query that you write over and over again, save it as a stored procedure, and then just call it to execute it.

You can also pass parameters to a stored procedure, so that the stored procedure can act based on the parameter value(s) that is passed.

A stored procedure is a precompiled set of one or more SQL statements that are stored on SQL Server. The benefit of Stored Procedures is that they are executed on the server-side and perform a set of actions, before returning the results to the client-side. This allows a set of actions to be executed with minimum time and also reduces the network traffic. Hence stored procedure improves performance to execute SQL statements.

Types of Stored Procedures

SQL Server divides the stored procedure into three major categories. Let us discuss each of them one by one:

1. System-defined SPs: They are defined by the system and it starts with the prefix “sp” mostly. It can be used to complete a variety of SQL tasks.
2. User-defined SPs: They are stored within a user database and created to perform a specific action. Here the prefix is different.
3. Extended SPs: They are calling functions from DLL files. Developers don’t rely on these procedures. So, it is better to avoid them and focus on the other two.

Now We will discuss about User-defined stored procedure. Later We will discuss about System-defined SP and Extended SP.

Example: Use this below Database

Code:

<span class="pln">

USE </span><span class="typ">TestDataBase</span><span class="pln">
</span>

Code: Create a table to understand stored procedure

<span class="pln">
CREATE TABLE tbl_customer
</span><span class="pun">(</span><span class="pln">
customerID INT PRIMARY KEY IDENTITY</span><span class="pun">(</span><span class="lit">100000000</span><span class="pun">,</span><span class="lit">1</span><span class="pun">),</span><span class="pln">
customerSSNId INT</span><span class="pun">,</span><span class="pln">
customerName VARCHAR</span><span class="pun">(</span><span class="lit">100</span><span class="pun">),</span><span class="pln">
customerAge </span><span class="kwd">int</span><span class="pun">,</span><span class="pln">
customerAddressLine1 VARCHAR</span><span class="pun">(</span><span class="lit">100</span><span class="pun">),</span><span class="pln">
customerAddressLine2 VARCHAR</span><span class="pun">(</span><span class="lit">100</span><span class="pun">),</span><span class="pln">
customerCityID VARCHAR</span><span class="pun">(</span><span class="lit">50</span><span class="pun">),</span><span class="pln">
customerStateID VARCHAR</span><span class="pun">(</span><span class="lit">50</span><span class="pun">)</span><span class="pln"> 
</span><span class="pun">)</span><span class="pln">

</span>

Code: Insert a record inside the table

<span class="pln">
INSERT INTO tbl_customer VALUES </span><span class="pun">(</span><span class="lit">12345678</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Rumman Ansari'</span><span class="pun">,</span><span class="pln"> </span><span class="lit">23</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Kolkata'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Rajarhat'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'12'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'29'</span><span class="pun">)</span><span class="pln">
</span>

Code: See the inserted record

<span class="pln">
</span><span class="typ">Select</span><span class="pln"> </span><span class="pun">*</span><span class="pln"> </span><span class="kwd">from</span><span class="pln"> tbl_customer
</span>

Example: Simple Store Procedure

Code: Create a store procedure which will select all custermer in the above table

<span class="pln">
</span><span class="com">/* Select All Customer 
Stored Procedure Example - 1 | Normal
*/</span><span class="pln">

CREATE PROCEDURE </span><span class="typ">SelectAllCustomers</span><span class="pln">
AS
SELECT </span><span class="pun">*</span><span class="pln"> FROM tbl_customer
GO</span><span class="pun">;</span><span class="pln">
</span>

Code: Execute your above created stored procedure.

<span class="pln">
EXEC </span><span class="typ">SelectAllCustomers</span><span class="pun">;</span><span class="pln">
</span>