Create Store Procedure in SQL Server

Rumman Ansari   2020-03-05   Student   SQL SERVER > Stored-Procedure   589 Share

In this blog we will show how to create a stored procesure and how to alter it

First We will Create a Table like below to understand the whole process

Code: 

<span class="pln">
USE </span><span class="typ">RummanTest</span><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>

Example: Insert Few records like below

Code: 

<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><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: Create Stored Procedure

Create Stored Procedure using simple rule 

<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">


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

Example: ALTER Stored Procedure

Example: alter Stored Procedure using simple rule

After creation of a stored procedure it is not possible to create that store procedure with same name, so in this case we have to alter the stored procedure everytime.

Code: In this case you have to use only ALTER to after modication.

<span class="pln">
</span><span class="com">/* Select All Customer 
Stored Procedure Example  with alter | after some changes in the stored procedure
*/</span><span class="pln">

ALTER PROCEDURE </span><span class="typ">SelectAllCustomers</span><span class="pln">
AS
SELECT customerID</span><span class="pun">,</span><span class="pln"> customerName  FROM tbl_customer
GO</span><span class="pun">;</span><span class="pln"> 

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

Example 2: Create store procedure

Example: Stored Procedure With One Parameter

<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">


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: Create store procedure

Example: Stored Procedure With Multiple Parameters

Code: 

<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

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>
Share this Post: