How to Create a Simple Stored Procedure in SQL

Creating a simple stored procedure without any parameters:

Example: Use this below Database

This stored procedure, retrieves all the employees. To create a stored procedure we use, CREATE PROCEDURE or CREATE PROC statement.

Code:

USE TestDataBase

Code: Create a table to understand stored procedure

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

Code: Insert a record inside the table

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

Code: See the inserted record

Select * from tbl_customer

Example 1: Simple Store Procedure

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

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

CREATE PROCEDURE SelectAllCustomers
AS
SELECT * FROM tbl_customer
GO;

Code: Execute your above created stored procedure.

You can just type the procedure name and press F5, or use EXEC or EXECUTE keywords followed by the procedure name as shown below.

SelectAllCustomers;

OR

EXEC SelectAllCustomers;

OR

Execute SelectAllCustomers;