Create a stored procedure with Sql Server
Tags: sql, sqlserver, database, backend
Published: 2023-10-07 20:44:51

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**](#what-are-stored-procedures)
* [**Setting up the Environment**](#setting-up-the-environment)
* [**Starting with the Basics**](#starting-with-the-basics)
* [**Stored Procedure Parameters**](#stored-procedure-parameters)
* [**Best Practices**](#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](https://www.microsoft.com/it-it/sql-server/sql-server-downloads?rtc=1)


![MSSQL download page](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/nlef8h8tck61ku30ibex.png)

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](https://learn.microsoft.com/it-it/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-ver16)


![SSMS download page](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/cfqiupowap2r6mq68epk.png)


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](https://www.microsoft.com/)**:

[Download .bak file from here](https://github.com/Microsoft/sql-server-samples/releases/tag/adventureworks)


![Repository AdventureWorks on GitHub](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/yeon47kxmobwnvm1z7pz.png)

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:


![Login to SSMS](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/0mx8meytzbiakn7ru6en.png)

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


![context menu for restore database](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/48w2y9dlmwwab3moxjjl.png)

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:


![database restored](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/6f9u14aix2qrh04ehf88.png)

### **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:

```sql

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`**:

```sql

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

- - Body of the stored procedure.

END;

```
For its deletion, instead:

```sql

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:


![New Query button](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/6azfa0lc07hsct5ajxkg.png)

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

```sql

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:


![data retrieved](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/rrkbvfy62vljehsc90fh.png)

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

```sql

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:

```sql

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:

```sql

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:

```sql

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:

```sql

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:


![Result from sp_InsertPerson Procedure](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ri4d1s3vz3pu3a9bp8xc.png)

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:

```sql

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:

```sql
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:

```sql

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:

```sql

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:

```sql

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:

```sql

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](https://docs.microsoft.com/en-us/sql/t-sql/statements/set-nocount-transact-sql).

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:

```sql

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!