One of the more useful constructs inside a SQL Server database is the stored procedure. These are code modules that combine multiple statements into one call that works similar to a function, procedure, or method in application code. There are a variety of reasons why stored procedures may be useful in your database development, and I hope that you choose to use these extensively rather than embedding every query into your application.

This article will discuss the structure of stored procedures and show you how to get started writing them for use in your application.

Basic Structure

A stored procedure consists of a name, parameters, and one or more lines of code that execute in the order they are written. As with many other objects, a stored procedure is contained inside a schema in your database and must have a unique identifier. We can create a stored procedure with the CREATE PROCEDURE DDL. Let's start with a very basic procedure.

CREATE PROCEDURE dbo.GetOne
AS
  SELECT 1

This will create a procedure that is named GetOne in the dbo schema. This contains one line of code, and if we execute this, it returns a single row result set.

This is not the most interesting or useful procedure, but it does illustrate a simple structure that contains a name, no parameters, and a single line of code.

Good Practices

While that procedure will work, we really want to do a better job of writing our T-SQL code in a database. Before we look in more detail about other options with stored procedures, let us examine a few good practices that will help you build useful stored procedures.

The first item I would mention is that it is preferable to include the BEGIN and END keywords to denote your code. While I can write a procedure like this, it is not a good practice:

When the next developer looks at this code, it is entirely possible they will assume the SELECT 2 is extraneous code in their editor window and delete this. We should make it clear what is a part of our procedure and what is not. To be clear, the procedure will include all code in the batch, so we do need to end the batch with a GO at the end of the stored procedure. This code should make that clear.

The second item is that we should ensure that any result set columns from our stored procedure have proper labeling. Most of the time this is handled by queries against existing columns. However, if we have aggregates or calculated columns, we should ensure that our application can easily reference the data in a column with a name. We will do this by including a name for the column in our query.

Lastly, it is a good practice to include some sort of comment inside the procedure that describes what the intent of the procedure is. This is different from describing the code, which may or may not do what we expect. Instead, describe the intent of the code, or what we expect should happen. This will help you and other developers build code that meets goals, as well as ensure the goal is documented in an explicit way.

Let's change our stored procedure to meet these goals. I will use the CREATE OR ALTER syntax, which was added in SQL Server 2016. Older versions of SQL Server would use the ALTER syntax only.

CREATE OR ALTER PROCEDURE dbo.GetOne
/*
Purpose: This procedure returns a scalar value of integer 1 under the name "One"
*/AS
BEGIN
      SELECT 1 AS One
END
GO 

Now we have a procedure that performs a function, is documented, and has the beginning and ending of code clearly delimited. Let's next move on to other options for our stored procedure.

Parameters

Often we want the behavior of our stored procedure to change slightly depending on what the application needs. We control this in stored procedures with parameters. A parameter is a value that is passed into the stored procedure as a part of the call. The basic structure is that after the name of the procedure, we can list the parameters we want, separated by commas, with the data type after each. This is very similar to the way in which we declare variables.

As an example, let's add two parameters to our stored procedure.

CREATE OR ALTER PROCEDURE dbo.GetOne @Param1 INT
                                   , @Param2 VARCHAR(100)
/*
Purpose: This procedure returns a scalar value of integer 1 under the name "One"
*/AS
BEGIN
    IF @Param1 = 10
      SET @Param1 = 100
    SELECT One = 1
         , @Param1 AS Param1
         , @Param2 AS Param2;
END;
GO
 

As you can see above, I have named my two parameters, @Param1 and @Param2. The @ symbol denotes this as a variable parameter that is named. The data type can be any of the valid data types in SQL Server, including a table type. I can use these parameters in my code. In this case, I have added an IF statement that resets the value of @Param1 if it is equal to 10. I then use both parameters as part of the query in my code.

There is really no practical limit for the number of parameters. There is a limit of 2100, but that is unlikely to be a practical level to use in any code. 

Parameter Defaults

When a parameter is placed in the header, it is like a variable that is defined but unitialized. This also means that the parameter is required. If you declare a parameter as a name and data type, without a default, a parameter value must be passed in the procedure call.

If you do not want to require parameters, or you are adding parameters to an existing procedure and are worried about breaking calling code that won't include the parameter, you can assign a default to the parameter. To do this, you can use an equals (=) sign after the data type and assign a value. Let's take the stored procedure above and add a few defaults.

CREATE OR ALTER PROCEDURE dbo.GetOne @Param1 INT = 1
                                   , @Param2 VARCHAR(100) = 'A default value'
   , @Param3 INT = null
/*
Purpose: This procedure returns a scalar value of integer 1 under the name "One"
*/AS
BEGIN
    IF @Param1 = 10
      SET @Param1 = 100
    SELECT One = 1
         , @Param1 AS Param1
         , @Param2 AS Param2
         , @Param3 AS Param3;
END;
GO 

I have added default values to the first two parameters. Each of these is set to a data value that matches the datatype. I have added a third parameter with a default value of NULL. This is often useful to detect if a calling program has actually passed in a parameter as NULL is rarely used.

When I execute this procedure, I have a few options. In the first call below, I have not passed in any parameters, so the default values are used. In the second call, I have passed two parameters, leaving the third as a default since it is not required. I could, of course, pass in all three parameter values.

Defaults can be any constant value that is valid for that particular datatype. These can include any valid wildcards as well.

Output Parameters

Often a stored procedure will return a result set if data is required back from the procedure, but it is possible to pass back parameter values to the calling code. We do this by including the OUTPUT keyword in the stored procedure call and in the calling code. Let's look at an example. First, I will alter the stored procedure definition above to pass @param1 back.

CREATE OR ALTER PROCEDURE dbo.GetOne @Param1 INT = 1 OUTPUT
                                   , @Param2 VARCHAR(100) = 'A default value'
   , @Param3 INT = null OUTPUT
/*
Purpose: This procedure returns a scalar value of integer 1 under the name "One"
*/AS
BEGIN
    IF @Param1 = 10
      SET @Param1 = 100
    SELECT One = 1
         , @Param1 AS Param1
         , @Param2 AS Param2
         , @Param3 AS Param3;
END;
GO

Now, in the calling code, we must declare a variable that we pass in as a parameter. This variable is then passed back to the calling code, with whatever value it contains at the end of the procedure. In this case we pass in two different parameters, @i and @j, one of which is changed by the stored procedure. The other is left alone.

In this case I still have a result set being returned from the stored procedure. I do not need to do this with output parameters, and can have a procedure that just changes parameters and does other work without returning a result set. 

If I call the procedure without using OUTPUT, I won't get the parameter value changed in the calling batch.

Other Options

There are other options that can be used when creating a stored procedure. I will describe a few of these, but there are many other options that can be used and are worthy of more detailed treatment in subsequent articles.

First is the RECOMPILE option. If you include this in the header of the stored procedure, the database engine will not keep a query plan for this procedure and compile a new one each time the procedure is called. This may be useful in some situations, but this is not recommended. The compilation time is often small compared to execution time, but this is not always the case. Use this option very carefully.

The ENCRYPTION option sounds good and does obfuscate the code of the stored procedure. If you do not use this option, the code you wrote for your stored procedure is stored in the sys.sql_modules DMV. You can see this below.

When the ENCRYPTION option is used, this isn't easily readable. If I encrypt the procedure, I'll see something like this. There is a NULL for the code. However, the code is there, and there are plenty 

Most of the time when a stored procedure is called, it does so under the security context of the caller. You can change that with the EXECUTE AS clause, which allows you to change that. There are a few options for this, but those are outside of the scope of this article. Be careful if you use the EXECUTE AS clause, and understand the implications of the various settings.

There are lots of other options, which I will attempt to cover in subsequent articles. If you are interested in doing more with a stored procedure, read the CREATE PROCEDURE docs.

Conclusion

Stored procedures allow a database to execute multiple lines of code with one call from an application. These can be any lines of T-SQL that you need to execute, including those that return result sets. You can update multiple tables, execute complex logic, and really complete any task you expect to do in a SQL Server database.

Learning to use this feature of SQL Server will allow you to embed more complex logic in applications, better decouple your database from your application code, improve security, and even modify the underlying schema without changing your application code. This article presents a few basics for building stored procedures and I would encourage you to practice writing your own to solve the database problems you encounter every day.