Create Stored procedure that accepts parameters in SQL Server

Updated on August 30, 2015 Kisan Patel

This tutorial will show you how to create stored procedure that accepts parameters to fetch data from database?

To create a stored procedure, expand the Programmability folder of the database and right click the Stored Procedures folder and choose Stored Procedure.

This will open up query window in right side with a template of stored procedure that looks like below−

Now modify it as per our own need.

In this case, we have changed the name to LoadPRegionDetails, added a @Age of integer type parameter. Parameter is optional in the stored procedure and more than one parameters can be added separated by comma (,). The parameter always follow by the data types.

Now write the SQL statements based on what should be the purpose of the stored procedure.

In this case, we are going to retrieve the data from Region table where Age is greater than the age passed as input parameter.

It is always recommended to write the Author, Create date and Description (purpose) of the stored procedure in the comment area at top so that it can be tracked down later on during modification, bug fixes etc. However, it is not mandatory. My stored procedure code looks like below.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Sheo Narayan
-- Create date: 17-Mar-2015
-- Description: This loads records from the PersonalDetails table based on Age
-- =============================================
CREATE PROCEDURE [dbo].[LoadPRegionDetails]
@Age int -- optional, there may not be even a single parameter, next parameter separated by comma
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT RegionID, RegionDescription, Age FROM Region WHERE Age > @Age
END