Stored Procedures In Mssql And .NET – Part I Parameters, Conditionals

In this first part of the the series, we are going to examine stored procedures in MSSQL, what benefits they offer, how to create and manage them. This series will be divided into four parts.

Firstly, I’ll try to show you different aspects of stored procedures that you may encounter in your daily work. Also, keep in mind that the first part is a brief post on stored procedures, not a deep diving guide, but a succinct one.

WHAT are Stored Procedures?

As you may already know that MSSQL uses a SQL dialect called T-SQL, store procedures use T-SQL statements, too.

Stored Procedures or sometimes referenced as “sprocs” are a group of T-SQL statements stored in the database. Stored Procedures can; accept parameters, return values, support variables, call other store procedures, etc. Stored Procedures are compiled once, the execution plan is created, cached and reused repeatedly by the MSSQL, offering performance gains(there are exceptions depending on the way of executing, check sp_executesql).. Also stored procedures help reducing network I/O.

BENEFITS OF USING STORED PROCEDURES

• Reusable
• Maintainable
• Secure
• Good at performing logic, whether simple or complex
• Supports temp tables and variables
• Accepts parameters
• Performance, compiled once reused repeatedly
• Reduces network I/O between database and application
• Other stored procedures can be called as well, known as nested procedures.

SAMPLE TABLEs

In four parts of the series I’ll be using this database.

In order to use sample stored procedures we need sample tables and data. Sample database is the famous good-old Microsoft Northwind database. Open the following address;

https://github.com/Microsoft/sql-server-samples/tree/master/samples/databases/northwind-pubs

Download only the “instnwnd.sql” file from the address. Use something like Azure Data Studio or Data Grip or MSSMS to execute that script. Also don’t forget to create a schema before running the script, my schema name is “TestDB”.

FIRST PROCEDURE

Let’s create our first procedure, this is a simple one, just to get started with syntax.

CREATE OR ALTER PROCEDURE GetProductsWithCategoryNames
AS
BEGIN 
    SELECT TOP (100) p.*, c.CategoryName
    FROM Products p 
    INNER JOIN Categories c ON p.CategoryID = c.CategoryID 
    ORDER BY p.ProductID DESC;
END 
GO

Our procedure starts with a “CREATE OR ALTER PROCEDURE” statement. This statement makes sure that if a stored procedure with the name of “GetProductsWithCategoryNames” does not exist, it will be created. If it already exists, it alters the current stored procedure. This syntax is available from the MSSQL 2016 version.

In a Stored Procedure “BEGIN” and “END” statements are optional, but it’s good practice to have  them. Between the BEGIN and END keywords, you can use T-SQL statements. As you can see, our stored procedure is fetching 100 records from the “Products” table with joining the “Categories” table, sorted by “ProductID” column in descending order.

We can execute our newly created stored procedure with the following statement.

USE TestDB;
EXEC GetProductsWithCategoryNames;

WITH PARAMETERS

Stored procedures allow us to use parameters. Parameters are divided into two categories; input and output parameters. Output parameters should have a distinguished OUT or OUTPUT keyword, whereas input parameters don’t need to have any specific keyword. A stored procedure which inserts a record into the “Customers” table with the input parameters representing the “Customers” table columns is as follows.

CREATE OR ALTER PROCEDURE InsertCustomer
(
	@CustomerID NCHAR(5),
	@CompanyName NVARCHAR(40),
	@ContactName NVARCHAR(30) = NULL,
	@ContactTitle NVARCHAR(30) = NULL,
	@Address NVARCHAR(60) = NULL,
	@City NVARCHAR(15) = NULL,
	@Region NVARCHAR(15) = NULL,
	@PostalCode NVARCHAR(10) = NULL,
	@Country NVARCHAR(15) = NULL,
	@Phone NVARCHAR(24) = NULL,
	@Fax NVARCHAR(24) = NULL
)
AS
BEGIN    
	INSERT INTO Customers
	  (CustomerID, CompanyName, ContactName, ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax)
	VALUES
	  (@CustomerID, @CompanyName, @ContactName, @ContactTitle, @Address, @City, @Region, @PostalCode, @Country, @Phone, @Fax);
   
END 
GO

As you can realize, except the “@CustomerID” and “@CompanyName” parameters, all other parameters are optional, we can assign default values to them. And all of them are input parameters. Remember, output parameters must have “OUT” or “OUTPUT” keyword following their name and types.

WITH CONDITIONALS

We can use conditionals in stored procedures as well and perform checks on the SQL side. Let’s inspect the newly created “InsertCustomer” procedure. We have two required parameter, which are “CustomerID” and “CompanyName” and we can check if they’re provided.  Like this.

CREATE OR ALTER PROCEDURE InsertCustomer
(
	@CustomerID NCHAR(5),
	@CompanyName NVARCHAR(40),
	@ContactName NVARCHAR(30) = NULL,
	@ContactTitle NVARCHAR(30) = NULL,
	@Address NVARCHAR(60) = NULL,
	@City NVARCHAR(15) = NULL,
	@Region NVARCHAR(15) = NULL,
	@PostalCode NVARCHAR(10) = NULL,
	@Country NVARCHAR(15) = NULL,
	@Phone NVARCHAR(24) = NULL,
	@Fax NVARCHAR(24) = NULL
)
AS
BEGIN
    IF(@CustomerID IS NOT NULL AND @CompanyName IS NOT NULL)
    BEGIN
	INSERT INTO Customers
	  (CustomerID, CompanyName, ContactName, ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax)
	VALUES
	  (@CustomerID, @CompanyName, @ContactName, @ContactTitle, @Address, @City, @Region, @PostalCode, @Country, @Phone, @Fax);
    END
END 
GO

We are performing an “IF” check inside the procedure, while we could also add an “ELSE” statement it’s not required, but it could be preferable in some cases. It’s also possible that we can perform nested conditional checks as well.

We can execute this stored procedure like this, only two required parameters provided.

USE TestDB;
EXEC InsertCustomer 'Test', 'Jackson Co. Inc.';

With appropriate checks in place, we can perform complex logic on the database side thanks to store procedures.

Next Parts

This Stored Procedure series will be divided into four parts. In the next part, I’ll dive into cursors, loops, error handling and transactions in stored procedures. Later on, performance and best practices will follow, I might add query profiling in MSSQL as well. Finally, we will be creating a data access library with the Dapper Micro ORM to use our stored procedures in .Net and C# applications.

Till then.