How to create a stored procedure (SQL -Server)
Stored procedures are just some precompiled queries that are stored within a database and executed within the database later. More details about stored procedures can be read in this article, but in this article, we are just learning how to create a stored procedure in SQL Server.
First of all, create a database and a table named [User] in order to follow the article as it is. Then follow the following steps in order to create a stored procedure that is used to get all the data in the [User] table.
The following image shows the template of the stored procedure that is given by Microsoft in order to ease the creation of stored procedures. There are some sections in the given template, and each section is explained in detail in the following paragraphs.
1. First of all, expand the database by clicking on the database name. [Image 01].
2. Then click on the programmability folder contained inside the database. [Image 02].
3. Then click on the stored procedures folder that’s situated inside the programmability folder. [Image 03].
4. If you do not have any created stored procedures within the database, you are only going to have a folder called system stored procedures. That’s folder contains some pre-defined stored procedures that are used for administrative and maintenance tasks within the SQL server instance. [Image 04]
5. Then right-click on the stored procedures folder and click New > Stored procedure in the menu that opens.
SectionA
First of all, let’s talk about Section A of the given template. (Section A in the above image). In the top of the stored procedure, it clearly says that this is a template generated using the template explorer, and it specifically says that this block of comments will not be included in the definition. So, we don’t have so many things to talk about in this section of the stored procedure.
After (section A), we can now talk about (section B), where there are some words that can be seen in the template, and they are known as some settings that can be set to on or off that can be used when we are calling the stored procedure in SQL Server. These settings can only be seen on Microsoft SQL Server.
SectionB
SET ANSI_NULLS ON | OFF
This setting is specific to Microsoft SQL Server, and this setting is used to control the way the database engine handles null values. That means when this setting is on, null values are treated as undefined values. Imagine the following. Table with a record null value on address Image
SELECT * FROM User WHERE Address = null
SET ANSI_NULLS ON
With this setting on, if we execute the stored procedure including the above query, there will be no rows selected, even though there is a record of a user whose address is null.
SET ANSI_NULLS OFF
With this setting off, if we execute the stored procedure, including the above query, there will be rows where the address is null on the table.
GO
Go statement is not a command yet; it is used to separate the batches on the SQL server, and when we separate batches, the current batch will inherit the settings from the previous batch.
SectionC
Section C is used to collect the details of the user.
Author: the developer who created the stored procedure.
Create Date: The date the developer created this stored procedure.
Description: A small description of the stored procedure; what does this stored procedure do. This stored procedure is used to retrieve data, save data, or update data.
SectionD
CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName>
This command is used to create the stored procedure in the database.
<@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>,
These are the parameters in the stored procedures. These are defined in this place and after the CREATE PROCEDURE command. Parameter names start with the @ symbol, and when we define these parameters, we need to specify the data type of the parameter, just like when we are declaring variables in programming languages.
AS
This AS keyword is used after the CREATE PROCEDURE command, and this keyword is used to introduce the body to the stored procedure. The body part contains the query for the stored procedure.
The following code snippet shows how the name of the stored procedure is set and how to set the parameters of the stored procedure.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Muditha Weerarathna>
-- Create date: <2024-01-19>
-- Description: <select all the users from the User table>
-- =============================================
CREATE PROCEDURE [dbo].[Get_Users] --Stored Procedure name with schema.
@Id INT --Parameter
AS
BEGIN
SET NOCOUNT ON;
--Query to select all the users from the [User] table.
SELECT *
FROM [User] U
WHERE U.Id = @Id
END
GOCheck out this GitHub Repository to learn more about the creation of stored procedures.
SectionE
BEGIN
This keyword is used to mark the beginning of a section or a block. That means, after defining the name of the stored procedure and the parameters of the stored procedure, the body of the stored procedure begins.
SET NOCOUNT ON
With this on, the stored procedure does not give the number of rows affected by the stored procedure. With this off, it will give the number of rows affected by the stored procedure. This is normally set off when we declare a stored procedure because the row count is not needed most of the time when we are using stored procedures, and it reduces network traffic too.
SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>
This is the query itself.
END
This keyword is used to mark the end of a section or a block, which means the query stops here. The body of the query stops here.
