Create a stored procedure with Sql Server

Hello everyone, today I would like to delve into a feature present in all databases and used by anyone who needs to approach server-side development: Stored Procedures.

In particular, this article will be focused on SQL Server databases, so while many concepts can be applied to other Relational Database Management Systems (RDBMS), many others will be specific to the T-SQL language.

Table of Contents:

• What are Stored Procedures
• Setting up the Environment
• Starting with the Basics
• Stored Procedure Parameters
• Best Practices

What are Stored Procedures

Stored Procedures (hereafter referred to as SP) are more or less complex instructions, depending on the problem they need to solve. They are saved in the cache of our databases, in this case, SQL Server, and can be reused as needed.

They are used for various purposes, but they typically come into play when we need certain operations to be executed quickly. Usually, the operations involved are either Data Manipulation Language (DML) operations or heavy data retrieval operations.

But what are the advantages of using them?

• It avoids rewriting complex queries, allowing the developer to reuse the code at any time.
• Greater query efficiency thanks to the fact that SQL Server can create increasingly efficient query execution plans.
• They reduce the amount of network traffic, for example:
  • Imagine having to create a new item in our table, wait for confirmation of the new item's creation, and then retrieve the newly created item. Instead of performing these operations sequentially, we could simply put the code into a SP and execute both the INSERT and SELECT operations all at once.

Are there any disadvantages? In this case, rather than disadvantages, I would call them best practices. In other words, you can do many things, but some things are better to avoid, for example:

• Returning an excessively large result set, i.e., too much information from a single Stored Procedure.
• Excessive use of cursors should be avoided, and it's preferable to use the Merge statement.

Setting up the Environment

First of all, make sure to download SQL Server 2022 (from now on referred to as MSSQL) and SQL Server Management Studio (from now on referred to as SSMS). I'm providing the links for you to download both:

SQL Server:

Download SQL Server HERE

Make sure to download the Developer edition and follow the installation wizard. Once you have completed the process, you can download SSMS:

Download Sql Server Management Studio

You'll also need to wait for the installation to be completed. In the meantime, you can start downloading the database that we will use for the experiments in this article from the AdventureWorks repository provided by Microsoft:

Download .bak file from here

Once you have downloaded the file and completed the installation, let's proceed to restore the database. Open SSMS and log in using Windows authentication:

Next, right-click on the 'Database' folder and then select 'Restore Database...':

In the 'General' page, you can choose the source as 'Device' and by clicking the 'Add' button, locate the .bak file that you downloaded earlier. Complete the operation by selecting the 'OK' buttons in succession, which will perform the database restore. If everything goes well, you should have this situation:

Starting with the Basics

The main commands for creating stored procedures are three:

• CREATE PROCEDURE to create a new procedure.
• ALTER PROCEDURE* to modify an existing procedure.
• DROP PROCEDURE to delete a procedure.

Essentially, the basic schema for creating a new stored procedure is as follows:

CREATE PROCEDURE <name of procedure>
- - Optional parameters.
AS
BEGIN

- -  Body of the stored procedure.

END;

As we can see, it's possible to assign parameters to our SP, just as we would do in any function or method of a class in any programming language. The words AS, BEGIN, and END are mandatory.

The names of SPs must be unique. In addition, I like to use a specific naming convention, which involves prefixing the name with "sp_" (of course, this should follow the project and team guidelines you are working with). Another thing that can help you in naming your SPs is assigning them a schema different from the typical "dbo." For those who may not know, a schema is similar to a namespace. This way, you can create SPs with the same name.

Modifying an SP is similar to creating one, with the only difference being that instead of CREATE, you will use the keyword ALTER:

ALTER PROCEDURE <name of procedure>
- - Optional parameters.
AS
BEGIN

- - Body of the stored procedure.

END;

For its deletion, instead:

DROP PROCEDURE <name of procedure>

Now, if we open our database and then navigate to the folders Programmability > StoredProcedures, we will already find some stored procedures there. Let's ignore them for now and create our custom ones (you can come back to them later). To get started, at the top, you'll find the 'New Query' button:

Click on the button to create a new page and insert the following query:

USE [AdventureWorks2022];
GO

SELECT * FROM [Person].[Person]

Using the 'Execute' button at the top or pressing the F5 key, we will execute the query, which will return a result set:

Now, if we want to create our first stored procedure based on this simple query, all we have to do is write these instructions:

USE [AdventureWorks2022];
GO

CREATE PROCEDURE sp_SelectPersons
AS
BEGIN
 SELECT * FROM [Person].[Person]
END;

and you will receive a message confirming the successful creation of our stored procedure. You will find it in the path Programmability > StoredProcedures. If you were to try running the same statement again, you would, of course, encounter an error because our SP already exists. Now, if you want to use it, it's quite simple; just execute the statement:

EXECUTE sp_SelectPersons;

and you will obtain the same result screen as before (link to the previous image).

Note that it is rarely necessary (but not impossible) to return all the columns of a table. Make it a good practice to select only the columns you need.

Based on this suggestion, let's try modifying our stored procedure in the following way:

USE [AdventureWorks2022];
GO

ALTER PROCEDURE sp_SelectPersons
AS
BEGIN
 SELECT 
    [FirstName],
    [MiddleName],
    [LastName]
 FROM [Person].[Person]
END;

se lanciamo nuovamente la query recupereremo solo quei tre campi.

One last basic piece of advice: if SSMS marks your SPs with a red error line, don't worry. Go to the top and click on the path:

Edit > Intellisense > Refresh Local Cache

The issue will be automatically resolved.

Stored Procedure Parameters

Now that we've covered the basics, let's take the next step, which is passing parameters to our SPs. There are three types of parameters:

• INPUT: These determine the values to pass to the SP.
• OUTPUT: Output parameters are useful when you want to obtain additional data or information from the database, in addition to the result set. They can be used to return calculated values, automatically generated identifiers, or other information.
• CUSTOM TYPE: Creating a custom data type.

If you recall the previous explanation, after naming them, you can also pass parameters in the following way:

CREATE PROCEDURE sp_InsertPerson
(
    @PersonType NCHAR(2), 
    @Title NVARCHAR(8), 
    @FirstName NVARCHAR(50),
    @MiddleName NVARCHAR(50),
    @LastName NVARCHAR(10), 
    @Suffix NVARCHAR(10), 
    @EmailPromotion INT, 
    @rowguid UNIQUEIDENTIFIER,
    @ModifiedDate DATETIME
)
AS
BEGIN

DECLARE @BusinnessEntityId INT;

INSERT INTO [Person].[BusinessEntity] ([rowguid], [ModifiedDate])
VALUES (@rowguid, @ModifiedDate)

SELECT @BusinnessEntityId = SCOPE_IDENTITY();

INSERT INTO [Person].[Person] ([BusinessEntityID], [PersonType], [Title], [FirstName], [MiddleName], [LastName], [Suffix], [EmailPromotion], [rowguid], [ModifiedDate])
VALUES (@BusinnessEntityId, @PersonType, @Title, @FirstName, @MiddleName, @LastName, @Suffix, @EmailPromotion, @rowguid, @ModifiedDate)

END;

The stored procedure executes two INSERT statements, one in the [Person].[BusinessEntity] table and one in the [Person].[Person] table. As you can see, we have created a variable (thank you, T-SQL) that, thanks to the SCOPE_IDENTITY() function, retrieves the Identity value from the first INSERT operation dynamically. This allows us to use that value to create a new person entity.

As you can see, we have enclosed all the various properties in parentheses and assigned them the correct data type to send. But now, how can we use this SP and pass the parameters it needs to execute? It's quite simple; the previous statement will be more than sufficient:

EXECUTE [dbo].[sp_InsertPerson] 'EM', NULL, 'Bruce', NULL, 'WAYNE', NULL, 1, '8A2E08B6-341E-488F-8BFF-C32477F4E626', '2023-10-07 14:51:10.540';

Now, let's execute a SELECT statement on the [Person].[BusinessEntity] and [Person].[Person] tables, and we will see that the fields have been correctly inserted:

If, on the other hand, you want to use OUTPUT parameters to retrieve, for example, the person with the longest name and the total count of people in our [Person].[Person] table, you would need to write an SP like this:

CREATE PROCEDURE sp_GetCustomerInformation
   @NumeroTotaleClienti INT OUTPUT,
   @ClienteNomePiuLungo NVARCHAR(50) OUTPUT
AS
BEGIN
   -- We calculate the total number of customers.
   SELECT @NumeroTotaleClienti = COUNT(*) FROM [Person].[Person];

   -- We find the customer with the longest name.
   SELECT TOP 1 @ClienteNomePiuLungo = FirstName
   FROM [Person].[Person]
   ORDER BY LEN(FirstName) DESC;
END

And then, you can execute it in the following way:

DECLARE @TotalClients INT;
DECLARE @LongestClientName NVARCHAR(50);

EXEC sp_GetCustomerInformation @TotalClients OUTPUT, @LongestClientName OUTPUT;

PRINT @TotalClients;
PRINT @LongestClientName;

The last case concerns the possibility of creating Custom data types that help us have a cleaner parameter management. As developers, what would we do in a common object-oriented language when we have too many parameters in our method? If the answer is that you would create an object to use as a parameter for the method, then not only have you answered correctly, but you have also grasped the concept.

The creation of a new type in MSSQL is possible thanks to the CREATE TYPE <name of type> statement, so now, let's create a new SP that will insert a new product into our table. First, let's create our custom data type 'Product' which will handle passing the parameters to our SP:

CREATE TYPE Product
AS TABLE 
(
    [Name] NVARCHAR(50) NOT NULL,
    [ProductNumber] NVARCHAR(25) NOT NULL,
    [MakeFlag] BIT NOT NULL,
    [FinishedGoodsFlag] BIT NOT NULL,
    [Color] NVARCHAR(15) NULL,
    [SafetyStockLevel] SMALLINT NOT NULL, 
    [ReorderPoint] SMALLINT NOT NULL,
    [StandardCost] MONEY NOT NULL,
    [ListPrice] MONEY NOT NULL,
    [Size] NVARCHAR(5) NULL, 
    [SizeUnitMeasureCode] NCHAR(3) NULL,
    [WeightUnitMeasureCode] NCHAR(3) NULL,
    [Weight] DECIMAL(8,2) NULL,
    [DaysToManufacture] INT NOT NULL,
    [ProductLine] NCHAR(2) NULL,
    [Class] NCHAR(2) NULL,
    [Style] NCHAR(2) NULL, 
    [ProductSubcategoryID] INT NULL, 
    [ProductModelID] INT NULL, 
    [SellStartDate] DATETIME NOT NULL, 
    [SellEndDate] DATETIME NULL, 
    [DiscontinuedDate] DATETIME NULL,
    [rowguid] UNIQUEIDENTIFIER NOT NULL, 
    [ModifiedDate] DATETIME NOT NULL
);

Let's EXECUTE it from our SSMS, and now our new data type will be available and accessible in the path:

Programmability > Types > User-Defined Table Types

Now, let's create our SP:

CREATE PROCEDURE sp_CreateNewProduct
(
 @product dbo.Product READONLY
)
AS
BEGIN

INSERT INTO [Production].[Product] 
([Name], [ProductNumber], [MakeFlag], [FinishedGoodsFlag], [Color], [SafetyStockLevel], [ReorderPoint], [StandardCost], [ListPrice], [Size], [SizeUnitMeasureCode], [WeightUnitMeasureCode], [Weight], [DaysToManufacture], 
[ProductLine], [Class], [Style], [ProductSubcategoryID], [ProductModelID], 
[SellStartDate], [SellEndDate], [DiscontinuedDate], [rowguid], [ModifiedDate])
SELECT
      [Name],
      [ProductNumber],
      [MakeFlag],
      [FinishedGoodsFlag],
      [Color],
      [SafetyStockLevel],
      [ReorderPoint],
      [StandardCost],
      [ListPrice],
      [Size],
      [SizeUnitMeasureCode],
      [WeightUnitMeasureCode],
      [Weight],
      [DaysToManufacture],
      [ProductLine],
      [Class],
      [Style],
      [ProductSubcategoryID],
      [ProductModelID],
      [SellStartDate],
      [SellEndDate],
      [DiscontinuedDate],
      [rowguid],
      [ModifiedDate]
   FROM @product;
END;

As you can see, passing parameters is much cleaner, and we can execute the INSERT using the SELECT statement rather than passing individual data to the statement as we would have done with input parameters. Now, let's try our new SP:

DECLARE @tempProduct dbo.Product;

INSERT INTO @tempProduct
VALUES 
('Prodotto3', '21543', 1, 1, 'Rosso', 10, 5, 10.00, 19.99, 'M', 'CM', 'KG', 2.5, 5, 'S', 'M', 'U', 1, 1, '2023-01-01', NULL, NULL, NEWID(), GETDATE()),
('Prodotto4', '87609', 0, 1, 'Blu', 12, 6, 8.50, 15.99, 'L', 'IN', 'LB', 3.0, 7, 'S', 'M', 'W', 2, 2, '2023-02-01', NULL, NULL, NEWID(), GETDATE());

EXECUTE sp_CreateNewProduct @product = @tempProduct;

Best Practices

Now, if you've been running our SPs, you might have noticed that in the terminal below the query execution window, we had messages notifying us of successful operations.

In a development environment, this isn't an issue, but in a production environment, there's no need for these messages. Is there a way to eliminate them? Absolutely! You can use MSSQL's SET Options, specifically the SET NOCOUNT option.

Let's modify our SP in the following way:

ALTER PROCEDURE sp_CreateNewProduct
(
  @product dbo.Product READONLY
)
AS
BEGIN

SET NOCOUNT ON;

INSERT INTO [Production].[Product] 
([Name], [ProductNumber], [MakeFlag], [FinishedGoodsFlag], [Color], 
[SafetyStockLevel], [ReorderPoint], [StandardCost], [ListPrice], [Size], 
[SizeUnitMeasureCode], [WeightUnitMeasureCode], [Weight],  [DaysToManufacture], 
[ProductLine], [Class], [Style], [ProductSubcategoryID], [ProductModelID], 
[SellStartDate], [SellEndDate], [DiscontinuedDate], [rowguid], [ModifiedDate])
SELECT
      [Name],
      [ProductNumber],
      [MakeFlag],
      [FinishedGoodsFlag],
      [Color],
      [SafetyStockLevel],
      [ReorderPoint],
      [StandardCost],
      [ListPrice],
      [Size],
      [SizeUnitMeasureCode],
      [WeightUnitMeasureCode],
      [Weight],
      [DaysToManufacture],
      [ProductLine],
      [Class],
      [Style],
      [ProductSubcategoryID],
      [ProductModelID],
      [SellStartDate],
      [SellEndDate],
      [DiscontinuedDate],
      [rowguid],
      [ModifiedDate]
FROM @product;

SET NOCOUNT OFF;

END;

If you now run our SP again, perhaps changing the product name, you will see that the messages are no longer displayed. You can find additional options here.

Another important best practice to use in our SPs is to use TRY/CATCH to handle errors. Let's modify our SP once again in the following way:

ALTER PROCEDURE sp_CreateNewProduct
(
   @product dbo.Product READONLY
)
AS
BEGIN

SET NOCOUNT ON;

BEGIN TRY

   INSERT INTO [Production].[Product] 
   ([Name], [ProductNumber], [MakeFlag], [FinishedGoodsFlag], 
   [Color], [SafetyStockLevel], [ReorderPoint], [StandardCost], 
   [ListPrice], [Size], [SizeUnitMeasureCode], 
   [WeightUnitMeasureCode], [Weight], [DaysToManufacture], 
   [ProductLine], [Class], [Style], [ProductSubcategoryID], 
   [ProductModelID], [SellStartDate], [SellEndDate], 
   [DiscontinuedDate], [rowguid], [ModifiedDate])
   SELECT
    [Name],
    [ProductNumber],
    [MakeFlag],
    [FinishedGoodsFlag],
    [Color],
    [SafetyStockLevel],
        [ReorderPoint],
    [StandardCost],
    [ListPrice],
    [Size],
    [SizeUnitMeasureCode],
    [WeightUnitMeasureCode],
    [Weight],
    [DaysToManufacture],
    [ProductLine],
    [Class],
    [Style],
    [ProductSubcategoryID],
    [ProductModelID],
    [SellStartDate],
    [SellEndDate],
    [DiscontinuedDate],
    [rowguid],
    [ModifiedDate]
  FROM @product;

END TRY
BEGIN CATCH
   PRINT 'Error in ' + ERROR_PROCEDURE() + '' + ERROR_MESSAGE();
   RETURN -1;
END CATCH;

SET NOCOUNT OFF;

END;

This implementation allows us to handle errors correctly.

Well, we've reached the end of this long journey, and I hope it has given you a good overview of what can be done with Stored Procedures in SQL Server databases.

If you enjoyed my article, which I sincerely hope you did, please give it a thumbs up. And if you have anything to share with me, please feel free to do so in the comments. I'm always looking to learn new things and discover new optimizations!

Happy Coding!