Sabari M Sabari M
Updated date Jan 07, 2021
In this blog, we will see how to create a simple SQL Server stored procedure with or without parameters. This blog will be helpful for those who want to create a SQL Server stored procedure for the first time and as well as for beginners.

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.

ABOUT THE AUTHOR

Sabari M
Sabari M
Software Professional, India

IT professional with 14+ years of experience in Microsoft Technologies with a strong base in Microsoft .NET (C#.Net, ASP.Net MVC, ASP.NET WEB API, Webservices,...Read More

https://www.techieclues.com/profile/alagu-mano-sabari-m

Comments (0)

There are no comments. Be the first to comment!!!