SQL Server > Stored Procedure

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.

-- =============================================
-- 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
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.

    -- Insert statements for procedure here
       SELECT PersonalDetailsId, FirstName, LastName, Age, Active FROM
PersonalDetails WHERE Age > @Age

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.

Now, go back to the Stored Procedures folder and right click and choose Refresh.

That will show the stored procedure we just created.

 Views: 1874 | Post Order: 87

Write for us