boutique replica bags up ideas

the best replique rolex and prices here.

julia highlight 99j hair color 10a quality straight human hair lace front wigs 8 - 24 inches pre plucked hairline 13x4 inches lace front brazilian wig onlinefor sale

Create Stored procedure that accepts parameters in SQL Server

Updated on     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.

storeprocedure

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

storeprocedure2

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

createSP

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

RefreshSP

That will show the stored procedure we just created.

ShowSP


SQL Server

Leave a Reply