Create Store Procedure in SQL Server

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:

USE RummanTest

CREATE TABLE tbl_customer
(
customerID INT PRIMARY KEY IDENTITY(100000000,1),
customerSSNId INT,
customerName VARCHAR(100),
customerAge int,
customerAddressLine1 VARCHAR(100),
customerAddressLine2 VARCHAR(100),
customerCityID VARCHAR(50),
customerStateID VARCHAR(50) 
)

Example: Insert Few records like below

Code:

INSERT INTO tbl_customer VALUES (12345678, 'Rumman Ansari', 23, 'Kolkata', 'Rajarhat', '12', '29')

Select * from tbl_customer

Example: Create Stored Procedure

Create Stored Procedure using simple rule

/* Select All Customer 
Stored Procedure Example - 1 | Normal
*/

CREATE PROCEDURE SelectAllCustomers
AS
SELECT * FROM tbl_customer
GO;


EXEC SelectAllCustomers;

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.

/* Select All Customer 
Stored Procedure Example  with alter | after some changes in the stored procedure
*/

ALTER PROCEDURE SelectAllCustomers
AS
SELECT customerID, customerName  FROM tbl_customer
GO; 

EXEC SelectAllCustomers;

Example 2: Create store procedure

Example: Stored Procedure With One Parameter

/* Select All Customer with where clause
Stored Procedure Example - 2 | Stored Procedure With One Parameter
*/


CREATE PROCEDURE SelectAllCustomers1 @Address1 nvarchar(30)
AS
SELECT * FROM tbl_customer WHERE customerAddressLine1 = @Address1
GO;


EXEC SelectAllCustomers1 @Address1 = "Kolkata"

Example 3: Create store procedure

Example: Stored Procedure With Multiple Parameters

Code:

/* Select All Customer with where clause
Stored Procedure Example - 3 | Stored Procedure With Multiple Parameters
*/

CREATE PROCEDURE SelectAllCustomers3 @Address1 nvarchar(30), @Name nvarchar(20)
AS
SELECT * FROM tbl_customer WHERE customerAddressLine1 =@Address1 AND customerName = @Name
GO

EXEC SelectAllCustomers3 @Address1 = "Kolkata", @Name = "Rumman Ansari"