Stored procedure that accepts parameters to fetch data in SQL Server

How to create stored procedure that accepts parameters to fetch data from Sql Server database?

Stored procedures are a special type of objects in SQL server where more than SQL statements are saved that is ready to execute.

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 LoadPersonalDetails, 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 PersonalDetails 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.

Our 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].[LoadPersonalDetails]
       @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 PersonalDetailsId, FirstName, LastName, Age, Active FROM
PersonalDetails WHERE Age > @Age
END

Now remove the template generator code and press F5 or Execute icon from the top – left of the toolbar and it should shows a success message as shown in the below picture.