Store Procedure in SQL Server with Example
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.
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 1: 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>
Example 2: Stored Procedure With One Parameter
Code: Create another stored procesure which will take parameter while execution
<span class="pln"> </span><span class="com">/* Select All Customer with where clause Stored Procedure Example - 2 | Stored Procedure With One Parameter */</span><span class="pln"> CREATE PROCEDURE </span><span class="typ">SelectAllCustomers1</span><span class="pln"> </span><span class="lit">@Address1</span><span class="pln"> nvarchar</span><span class="pun">(</span><span class="lit">30</span><span class="pun">)</span><span class="pln"> AS SELECT </span><span class="pun">*</span><span class="pln"> FROM tbl_customer WHERE customerAddressLine1 </span><span class="pun">=</span><span class="pln"> </span><span class="lit">@Address1</span><span class="pln"> GO</span><span class="pun">;</span><span class="pln"> </span>
Code: Execute the above stored procedure
<span class="pln"> EXEC </span><span class="typ">SelectAllCustomers1</span><span class="pln"> </span><span class="lit">@Address1</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> </span><span class="str">"Kolkata"</span><span class="pln"> </span>
Example 3: Stored Procedure With Multiple Parameters
Code: Create a Store Procedure which will take multiple parameter
Setting up multiple parameters is very easy. Just list each parameter and the data type separated by a comma as shown below.
<span class="pln"> </span><span class="com">/* Select All Customer with where clause Stored Procedure Example - 3 | Stored Procedure With Multiple Parameters */</span><span class="pln"> CREATE PROCEDURE </span><span class="typ">SelectAllCustomers3</span><span class="pln"> </span><span class="lit">@Address1</span><span class="pln"> nvarchar</span><span class="pun">(</span><span class="lit">30</span><span class="pun">),</span><span class="pln"> </span><span class="lit">@Name</span><span class="pln"> nvarchar</span><span class="pun">(</span><span class="lit">20</span><span class="pun">)</span><span class="pln"> AS SELECT </span><span class="pun">*</span><span class="pln"> FROM tbl_customer WHERE customerAddressLine1 </span><span class="pun">=</span><span class="lit">@Address1</span><span class="pln"> AND customerName </span><span class="pun">=</span><span class="pln"> </span><span class="lit">@Name</span><span class="pln"> GO </span>
Code: Execute the above store procedure
<span class="pln"> EXEC </span><span class="typ">SelectAllCustomers3</span><span class="pln"> </span><span class="lit">@Address1</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> </span><span class="str">"Kolkata"</span><span class="pun">,</span><span class="pln"> </span><span class="lit">@Name</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> </span><span class="str">"Rumman Ansari"</span><span class="pln"> </span>
Example 4: Stored Procedure With OUT Parameters
Code: Create another store procedure which will return a parameter as output
<span class="pln"> </span><span class="com">/* Insert into Customer table with data records Stored Procedure Example - 4 | Stored Procedure With Out Parameters */</span><span class="pln"> CREATE PROCEDURE sp_insertIntoTable </span><span class="pun">(</span><span class="pln"> </span><span class="lit">@customerSSNId</span><span class="pln"> INT</span><span class="pun">,</span><span class="pln"> </span><span class="lit">@customerName</span><span class="pln"> VARCHAR</span><span class="pun">(</span><span class="lit">100</span><span class="pun">),</span><span class="pln"> </span><span class="lit">@customerAge</span><span class="pln"> </span><span class="kwd">int</span><span class="pun">,</span><span class="pln"> </span><span class="lit">@customerAddressLine1</span><span class="pln"> VARCHAR</span><span class="pun">(</span><span class="lit">100</span><span class="pun">),</span><span class="pln"> </span><span class="lit">@customerAddressLine2</span><span class="pln"> VARCHAR</span><span class="pun">(</span><span class="lit">100</span><span class="pun">),</span><span class="pln"> </span><span class="lit">@customerCityID</span><span class="pln"> VARCHAR</span><span class="pun">(</span><span class="lit">50</span><span class="pun">),</span><span class="pln"> </span><span class="lit">@customerStateID</span><span class="pln"> VARCHAR</span><span class="pun">(</span><span class="lit">50</span><span class="pun">),</span><span class="pln"> </span><span class="lit">@customerIDOut</span><span class="pln"> INT OUT </span><span class="pun">)</span><span class="pln"> AS </span><span class="kwd">BEGIN</span><span class="pln"> INSERT INTO tbl_customer VALUES </span><span class="pun">(</span><span class="pln"> </span><span class="lit">@customerSSNId</span><span class="pun">,</span><span class="pln"> </span><span class="lit">@customerName</span><span class="pun">,</span><span class="pln"> </span><span class="lit">@customerAge</span><span class="pun">,</span><span class="pln"> </span><span class="lit">@customerAddressLine1</span><span class="pun">,</span><span class="pln"> </span><span class="lit">@customerAddressLine2</span><span class="pun">,</span><span class="pln"> </span><span class="lit">@customerCityID</span><span class="pun">,</span><span class="pln"> </span><span class="lit">@customerStateID</span><span class="pln"> </span><span class="pun">)</span><span class="pln"> SET </span><span class="lit">@customerIDOut</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> </span><span class="pun">@</span><span class="lit">@IDENTITY</span><span class="pln"> </span><span class="kwd">END</span><span class="pln"> </span>
Example 5: Stored Procedure With OUTPUT Parameters
If you declare a parameter as OUTPUT, it acts as Both Input and OUTPUT
Code: Execute the above stored procedure
<span class="pln"> </span><span class="pun">--</span><span class="pln"> </span><span class="typ">Execute</span><span class="pln"> DECLARE </span><span class="lit">@customerIDOut1</span><span class="pln"> INT EXEC sp_insertIntoTable </span><span class="lit">12345678</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Ram 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><span class="lit">@customerIDOut1</span><span class="pln"> OUT PRINT </span><span class="lit">@customerIDOut1</span><span class="pln"> </span><span class="pun">--</span><span class="pln"> </span><span class="typ">See</span><span class="pln"> table content SELECT </span><span class="pun">*</span><span class="pln"> FROM tbl_customer </span>
Code: Create a stored procedure which will take a input parameter as well as give a output parameter
<span class="pln"> </span><span class="com">/* Insert into Customer table with data records Stored Procedure Example - 5 | Stored Procedure With Output Parameters */</span><span class="pln"> ALTER PROCEDURE sp_insertIntoTable1 </span><span class="pun">(</span><span class="pln"> </span><span class="lit">@customerSSNId</span><span class="pln"> INT OUTPUT</span><span class="pun">,</span><span class="pln"> </span><span class="lit">@customerName</span><span class="pln"> VARCHAR</span><span class="pun">(</span><span class="lit">100</span><span class="pun">),</span><span class="pln"> </span><span class="lit">@customerAge</span><span class="pln"> </span><span class="kwd">int</span><span class="pun">,</span><span class="pln"> </span><span class="lit">@customerAddressLine1</span><span class="pln"> VARCHAR</span><span class="pun">(</span><span class="lit">100</span><span class="pun">),</span><span class="pln"> </span><span class="lit">@customerAddressLine2</span><span class="pln"> VARCHAR</span><span class="pun">(</span><span class="lit">100</span><span class="pun">),</span><span class="pln"> </span><span class="lit">@customerCityID</span><span class="pln"> VARCHAR</span><span class="pun">(</span><span class="lit">50</span><span class="pun">),</span><span class="pln"> </span><span class="lit">@customerStateID</span><span class="pln"> VARCHAR</span><span class="pun">(</span><span class="lit">50</span><span class="pun">),</span><span class="pln"> </span><span class="lit">@customerIDOut</span><span class="pln"> INT OUT </span><span class="pun">)</span><span class="pln"> AS </span><span class="kwd">BEGIN</span><span class="pln"> INSERT INTO tbl_customer VALUES </span><span class="pun">(</span><span class="pln"> </span><span class="lit">@customerSSNId</span><span class="pun">,</span><span class="pln"> </span><span class="lit">@customerName</span><span class="pun">,</span><span class="pln"> </span><span class="lit">@customerAge</span><span class="pun">,</span><span class="pln"> </span><span class="lit">@customerAddressLine1</span><span class="pun">,</span><span class="pln"> </span><span class="lit">@customerAddressLine2</span><span class="pun">,</span><span class="pln"> </span><span class="lit">@customerCityID</span><span class="pun">,</span><span class="pln"> </span><span class="lit">@customerStateID</span><span class="pln"> </span><span class="pun">)</span><span class="pln"> SET </span><span class="lit">@customerIDOut</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> </span><span class="pun">@</span><span class="lit">@IDENTITY</span><span class="pln"> SET </span><span class="lit">@customerSSNId</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> </span><span class="lit">@customerSSNId</span><span class="pun">+</span><span class="lit">1</span><span class="pln"> </span><span class="kwd">END</span><span class="pln"> </span>
Code: Execute the above stored procedure
<span class="pln"> </span><span class="pun">--</span><span class="pln"> </span><span class="typ">Execute</span><span class="pln"> DECLARE </span><span class="lit">@customerIDOut1</span><span class="pln"> INT</span><span class="pun">,</span><span class="pln"> </span><span class="lit">@customerSSNId1</span><span class="pln"> INT </span><span class="pun">=</span><span class="pln"> </span><span class="lit">12345678</span><span class="pln"> EXEC sp_insertIntoTable1 </span><span class="lit">@customerSSNId1</span><span class="pln"> OUTPUT</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Ram 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><span class="lit">@customerIDOut1</span><span class="pln"> OUT PRINT </span><span class="lit">@customerIDOut1</span><span class="pln"> PRINT </span><span class="lit">@customerSSNId1</span><span class="pln"> </span><span class="pun">--</span><span class="pln"> </span><span class="typ">See</span><span class="pln"> the table content SELECT </span><span class="pun">*</span><span class="pln"> FROM tbl_customer </span>
