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.
Now, go back to the Stored Procedures folder and right click and choose Refresh.
That will show the stored procedure we just created.
Views: 21206 | Post Order: 87