Introduction

A stored procedure is a group of one or more SQL statements that are stored in the database and can be executed in many ways. You can easily create a stored procedure in MSSQL server using Microsoft SQL Server Management Studio. In this blog, we will see how to create a simple stored procedure in an SQL server.

Stored Procedure Without Parameters:

You can create a stored procedure if you want to retrieve data, insert or update or delete data from the database. In this example, we will see how to retrieve all data or records from dbo.Employee table that is stored under EmployeeContextDb database using a stored procedure.

Below SELECT statement is used to retrieve all rows or records from the dbo.Employee table.

// Select database
Use EmployeeContextDb
// Retrieve all records from Employee table
Select * from dbo.Employee

We will create a stored procedure "GetEmployee" as shown below which returns all rows from the dbo.Employee table. We don't need to pass any parameters since it returns all rows from the table.

USE EmployeeContextDb
GO

CREATE PROCEDURE dbo.GetEmployees
AS
Begin
	SELECT * FROM dbo.Employee
End
GO

Execute Stored Procedure :

There are three ways to execute a store procedure as shown below,

EXEC GetEmployees
-- OR
EXEC dbo.GetEmployees
-- OR
GetEmployees

Output:

Once you executed the above stored procedure, you will get all rows from the dbo.Employee table.

Stored Procedure With Parameters:

SQL Server stored procedure also accepts parameters. If we have a requirement to pass parameters to filter the rows or update the records or delete, then we can use stored procedures with parameters.

If we want to select only one employee record from dbo.Employee table then we can use below query with where condition.

Use EmployeeContextDb
Go
// Use where condition to select a particular record or row
SELECT * FROM dbo.Employee where id = 3

So how to create a stored procedure for the above query? Its very simple to create a stored procedure with parameters.

Use EmployeeContextDb
Go

CREATE PROCEDURE dbo.GetEmployeeById
@Id		int  -- Parameter
AS
Begin
	SELECT * FROM dbo.Employee where id = @Id  // assigned the parameter here
End
GO

Execute Stored Procedure :

In order to execute the stored procedure, you have to pass the parameter value after the stored procedure name as shown below, In this example, we are passing "3" as a parameter value to get the corresponding employee record.

-- 3 is the parameter value here
EXEC GetEmployeeById 3 
-- OR
EXEC dbo.GetEmployeeById 3
-- OR
GetEmployeeById 3

Output:

You will get one employee record as shown below after the execution of above stored procedure.