SQL Stored Procedure Input and Output Parameters, Types, Error Handling, Security and more

By: Aubrey Love   |   Updated: 2022-10-04   |   Comments (2)   |   Related: > Stored Procedures

Problem

I have heard about SQL Server stored procedures, but I don't know exactly what they are and how to best use them. In this tutorial, we will cover various things you should know about getting started with stored procedures.

Solution

In this tutorial, you will learn what a Stored Procedure is, how to create a Stored Procedure, and how to implement a Stored Procedure. We will also cover Stored Procedure parameters, both input and output, as well as Stored Procedures with multiple parameters. We will follow up with a brief look at error handling and security for Stored Procedures.

Prerequisites

The examples in this tutorial will be against the AdventureWorks sample SQL database. Once you have practiced with the sample database, the examples below should be straightforward and can be applied to your database. As always, please do not experiment with your production system.

What is a SQL Stored Procedure?

In its simplest form, a Stored Procedure is nothing more than a collection of Transact-SQL (T-SQL) code that is stored in a database and can be executed when called by the Stored Procedure name. The Stored Procedure name is assigned when creating it. Stored Procedures are often called SPs for short.

SQL Stored Procedure Points of Interest

• Stored Procedures can accept input parameters.
• Stored Procedures can return output parameters.
• Stored Procedures contain programming statements.
• Stored Procedures can return a status value indicating success or failure (and why it failed).
• There is no predefined maximum size for a Stored Procedure.
• There are four types of Stored Procedures:
  1. User-Defined
  2. Temporary
  3. System
  4. Extended User-Defined

Defining the Four Types of Stored Procedures

User-Defined Stored Procedure

The user-defined Stored Procedure is the most commonly used. It can be created in a user-defined database or any system database except for the Resource Database. We will touch more on the user-defined Stored Procedure later in this article with some samples for you to try.

Temporary Stored Procedure

Temporary Stored Procedures are a form of user-defined Stored Procedure and are very similar to Temporary Tables. Temporary Stored Procedures are stored in the "tempdb" database under the "System Databases" tab. These temporary Stored Procedures can be used locally or as a global Stored Procedure.

Temporary Local Stored Procedures are visible to the current user connection only and will always be prefixed with a # (pound sign, aka hashtag).

Temporary Global Stored Procedures are visible to any user connection and are prefixed with ## (two pound signs or hashtags).

Since they are "temporary", these Stored Procedures will go away when the SQL connection is closed.

System Stored Procedure

You will find yourself using System Stored Procedures quite often. It's a good idea to get familiar with them so you are not reinventing the wheel, so to speak. These Stored Procedures are included by default with the installation of SQL Server and will always begin with the prefix of "sys.sp_". Some of the most common system Stored Procedures you may find useful include sys.sp_addUser, sys.sp_addLogin, sys.sp_addRole, sys.sp_change_users_login, etc.

You can view the complete list of pre-installed system Stored Procedures by opening the Object Explorer in SQL Server Management Studio (SSMS) and expanding the following folders in this order:

1. Databases
2. System Databases
3. Tempdb
4. Programmability
5. Stored Procedures
6. System Stored Procedures

See the image below.

Extended User-Defined Stored Procedure

The Extended User-Defined Stored Procedure is used to create external routines in languages like C, C#, VB, etc. They are implemented as DLLs that SQL Server can load and run dynamically.

However, according to Microsoft, the Extended User-Defined Stored Procedure will be removed from future versions of SQL Server. With that said, it is recommended that you not use these Stored Procedures in any current or future development work. You should also start planning to remove or modify any applications currently using these Stored Procedures.

Why Use a Stored Procedure

Stored Procedures offer many benefits. They reduce the network traffic since the name is the only thing passed through the network from the application code. True, we may pass a few parameters with it, but passing a Stored Procedure name and a few parameters is not as much of a network hog as it is to pass the entire block of code over the network every time you need to run that code.

Stored Procedures offer stronger security. The code in the Stored Procedure determines what actions are taken on database objects. This means that every time we call a Stored Procedure, the same block of code is executed in the exact same way. You don't have to worry about making a mistake in the code when repeatedly re-typing the same code each time you need to execute it.

We can grant users permission to use (call) the Stored Procedure or deny users the ability to call a Stored Procedure. Note: The user does not have to have permission or access to a table to call the Stored Procedure that will modify the data on that table. This will allow users to add data to a table for general data entry while denying them access to view, delete, or alter any data in the table other than what you have given them permission.

Once you create a Stored Procedure and run it, a query execution plan is created that is stored and reused. If you create a basic template for your SQL code or type it in manually each time you need to execute that code, SQL must create a new query execution plan. This can slow your code execution time and consume more resources. Since we are storing and reusing the existing execution plan, the Stored Procedure can run much faster and use fewer resources because it does not need to create an execution plan. It simply loads the one that was created earlier.

Creating a Simple Stored Procedure

Now that we know a little bit about the benefits of Stored Procedures, let's start by creating a basic test sample to work with. In the sample code below, we are creating a fundamental Stored Procedure named uspGetEmployees. I'm sure you can guess what we mean with the "Get Employees" part, but what about that "usp" part of the name? You can name the Stored Procedure anything you like, but the industry standard is to use the convention we laid out here. "usp" is an initialism for "User Stored Procedure".

USE [AdventureWorks2019]
GO
CREATE PROCEDURE uspGetEmployees
AS
SELECT
   BusinessEntityID
   , FirstName
   , LastName
FROM Person.Person;
GO

As you can see from the code block above, creating a Stored Procedure is very much like creating a VIEW in SQL Server. You prefix the SELECT statement with "CREATE PROCEDURE <StoredProcedureName>".

Note: When creating a Stored Procedure, you can spell out the word "PROCEDURE" or abbreviate it to "PROC"; either option will work.

Execute Stored Procedures

When you execute (run) a Stored Procedure, you simply need to type the command EXECUTE (or EXEC) followed by the Stored Procedure name, as in the example SQL statement below.

EXEC uspGetEmployees;

Results: (Partial)

Note: In most cases, you don't need to use the EXEC command to run a Stored Procedure. You can type the name of the Stored Procedure and run it. You will get the same results either way. For clarity's sake, we will use the EXEC command throughout this article.

Altering / Modifying a Stored Procedure

You can use the "ALTER PROC" or "ALTER PROCEDURE" command to change how the Stored Procedure functions. Let's say we also want to retrieve the middle name from the "Person.Person" table in the AdventureWorks database. Rather than creating a new Stored Procedure, we can modify the existing one, as in the example syntax below.

ALTER PROC uspGetEmployees
AS
SELECT
   BusinessEntityID
   , FirstName
   , MiddleName
   , LastName
FROM Person.Person;
GO

Let's run the Stored Procedure again to see the new results.

EXEC uspGetEmployees;

Results: (Partial)

As you can see, the only difference between this result set and the previous one is that we now have the middle name in our results.

Okay, pretty simple. Let's do one more for practice. This time, we will add a WHERE clause and use the IS NULL parameter to filter out any employee with a NULL value for their middle name.

ALTER PROC uspGetEmployees
AS
SELECT
   BusinessEntityID
   , FirstName
   , MiddleName
   , LastName
FROM Person.Person
WHERE MiddleName IS NOT NULL;
GO

Let's run that same Stored Procedure again and see the new result set.

EXEC uspGetEmployees;

Results: (Partial)

Dropping a Stored Procedure

Dropping or deleting a Stored Procedure in SQL is like dropping a TABLE, VIEW, etc. You simply call the "DROP PROCEDURE" or "DROP PROC" command followed by the Stored Procedure name.

DROP PROC uspGetEmployees;

Make sure you want to drop (delete) a Stored Procedure before removing it. Stored Procedures, when not in use, consume little to no resources and offer no reason to drop them just because you think they might be in the way. If you're on the fence about dropping a Stored Procedure, create a template with the Stored Procedure contents so you don't have to re-write all that code at a later date.

Stored Procedure Input Parameters

So, what is an Input Parameter? In its simplest form, it's a variable. When we add a variable to a Stored Procedure, it is referenced as an input parameter, not a variable, although it's the same. Just think of it as a naming convention to differentiate between the two.

When we add an input parameter to a Stored Procedure, you place it under the "CREATE PROC" command and above the "AS" command. And like any variable, the variable name must be preceded by a @ sign and followed by the data type. In the following example, we are altering our current Stored Procedure by adding a variable and assigning the variable a default value.

ALTER PROCEDURE uspGetEmployees 
  @lastname VARCHAR(40) = 'abel'
AS
SELECT
   BusinessEntityID
   , FirstName
   , LastName
FROM Person.Person
WHERE LastName = @lastname;
GO

Now, let's run this Stored Procedure to see our results.

EXEC uspGetEmployees;

Results:

Okay, that's good, but what if I want to call a different name for my parameter? Then you simply need to supply that variable after your Stored Procedure name when calling that Stored Procedure like in the example below. This will use the value you provide instead of the default value.

EXEC uspGetEmployees 'Akers';

Results:

Okay, but what if you are unsure how the name is spelled? Well, for that, we will alter the Stored Procedure again. This time, we will replace the = (equal) sign with LIKE in our WHERE clause.

ALTER PROCEDURE uspGetEmployees
  @lastname VARCHAR(40) = 'abel'
AS
SELECT
   BusinessEntityID
   , FirstName
   , LastName
FROM Person.Person
WHERE LastName LIKE @lastname;
GO

This will allow us to use wildcards in our parameter and return, for example, everyone with "Ab" as the first two letters of their last name.

EXEC uspGetEmployees 'Ab%';

Results:

Stored Procedure Multiple Input Parameters

Adding multiple parameters follows the same principles as the single parameter command, but they must be separated by a comma. In the example below, we are adding a "FirstName" parameter, and we need to add a reference to that parameter in the WHERE clause. We start by removing the default values that we assigned to the parameters in the previous examples. These values will be provided by the user when calling the Stored Procedure.

ALTER PROCEDURE uspGetEmployees
  @lastname VARCHAR(40)
, @firstname VARCHAR(40)
AS
SELECT
   BusinessEntityID
   , FirstName
   , LastName
FROM Person.Person
WHERE LastName LIKE @lastname AND FirstName LIKE @firstname;
GO

Now, when we run this Stored Procedure, we need to include the values for both parameters. In this example, we will return all the rows where the last name starts with "Ab", and the first name begins with "K".

EXEC uspGetEmployees 'Ab%', 'K%';

Results:

When you have multiple parameters, the values you provide when running the Stored Procedure must be in the same order as those listed in the Stored Procedure you created. However, there is a workaround for this. In our example, let's say that I want to switch the order of the names and specify the First Name followed by the Last Name when I run the Stored Procedure. We can do this by specifying the variable @FirstName followed by the variable @LastName, like in the example below.

EXEC uspGetEmployees @FirstName = 'Kim', @LastName = 'Ab%';

Results:

Usually, this is not something you would do. But, if you are not sure of the order that the parameters are listed, you can use this option to ensure you get the desired results. Otherwise, if you reverse the order of the parameters when you call the Stored Procedure and don't specify the variable name, the query will not return the correct results. Since our Stored Procedure is looking for the Last Name and then the First Name, in that order, like in our example, it will not return anything because there is no one in the table with the last name of Kim and the first name of Abercrombie.

Stored Procedure Output Parameters

Output parameters can be a bit tricky. We will start with a very simple example so you can grasp the basics of creating an output parameter in a Stored Procedure. The code block below is not something you can copy and paste into your query editor easily. In this example, I have included line numbers for easy reference when explaining each step for the output parameters example.

1) CREATE PROC myRowCount
2) @lname VARCHAR(40), 
3) @numrows INT = 0 OUTPUT 
4) AS
5) SELECT LastName
6) FROM Person.Person 
7) WHERE LastName LIKE @lname
8) SET @numrows = @@ROWCOUNT; 

Below is the breakdown for creating a Stored Procedure with an output parameter line by line, as depicted in the code block above.

1. We are creating a new Stored Procedure called "myRowCount".

2. Add an input parameter called "@lname" with a datatype of "VARCHAR(40)".

3. Now, let's add an output parameter named "@numrows" because we want to count the rows returned with the Stored Procedure. We will give it a datatype of "INT", a value of zero, and we need to specify that it is an output with the keyword OUTPUT. This will let SQL know that this particular variable will be used to output data.

4.  This is our "AS" keyword that says create this Stored Procedure "as" the following code.

5.  Our basic SELECT statement.

6.  The FROM clause indicates the table where we want to get the data.

7.  Our WHERE clause sets the preliminary filters on our SELECT statement.

8.  In this line, we are going to set (assign) the "@numrows" variable to "@@ROWCOUNT". Any time you see two @ signs, that's a system variable, and there are several of these. Become familiar with system variables. Since the @@ROWCOUNT loads every time a query is executed, we capture the count generated from running our Stored Procedure and load that value into our output parameter @numrows.

Running a Stored Procedure with an output parameter is not as simple as calling the Stored Procedure name. Like we did with the Stored Procedure containing an input parameter, we will need to add additional conditions and values to the execute command. The code block below shows how to execute our Stored Procedure properly. The numbered paragraph below it will explain what each step is doing.

1) DECLARE @retrows INT
2) EXEC myRowCount 'B%', @numrows = @retrows OUTPUT
3) SELECT @retrows AS 'Rows'; 

1. Here, we declare a variable to catch whatever comes back from the Stored Procedure as an output variable. In this example, we are naming the new variable as "@retrows", short for "return rows". We must assign it a data type that matches the output variable we created in our Stored Procedure.
2. This is where we execute our Stored Procedure with the "EXEC myRowCount" command, and we need to satisfy the input parameter requirements by adding a value. In this example, we want to return all the rows where the last name begins with "B". Following that, on the same line, we want to get the values in our variable "@numrows" that we created in the Stored Procedure and put that value in our new variable that we created with our "DECLARE" statement in the line above. Again, we need to add the OUTPUT keyword to remind the compiler of this variable.
3. Now, we create a final SELECT statement where we get the value (row count) from our "@retrows" variable that we just declared, and we will name the result set "Rows".

When you run this query, you should see two result sets returned. The first will return all the names in the table that begin with the letter "B"; it should be 1,205. The second result set shows that count.

Results:

Handling Errors in Stored Procedures

In this section, we are again altering the Stored Procedure uspGetEmployees. Using the @@ROWCOUNT function, as explained in the previous section, we will use that to return a message "No Records Found" if nothing is returned. A bit of a simple but effective look at error handling with Stored Procedures.

Here we will run the Stored Procedure twice. The first will return all rows where the last name begins with "Ak"; the second time we run the Stored Procedure, it will return our error message. The first thing we would need to do is alter the Stored Procedure.

ALTER PROCEDURE uspGetEmployees
@lastname VARCHAR(40)
AS
SELECT
   BusinessEntityID
   , FirstName
   , LastName
FROM Person.Person
WHERE LastName LIKE @lastname
IF @@ROWCOUNT < 1
BEGIN
   RAISERROR('No Records Found', 10,1)
END;
GO

Now, let's run our Stored Procedure while looking for a name that we know is in the Person.Person table.

EXEC uspGetEmployees 'Ak%';

Results:

Next, let's run the same Stored Procedure with a name we know is not in the Person.Person table.

EXEC uspGetEmployees 'zz%';

Results:

Although this was a basic example of error handling with Stored Procedures, it will give you an example of what you can do in your test and work environment. Experiment around and make some changes. Make the error handling more intuitive and see what you can do with error handling in your Stored Procedures.

Stored Procedure Security

Stored Procedures offer many security advantages over regular T-SQL queries. When a T-SQL query is written into a Stored Procedure, the user cannot manipulate the code but simply run it as it is. Below is a list of benefits Stored Procedures have over hand-typed T-SQL code.

• Abstraction: Users can execute complex queries without knowledge of the underlying table structure or other database objects.
• Encryption: You can encrypt the code in a Stored Procedure after creating it.
• SQL Injection Prevention: All parameters passed into Stored Procedures can be validated before being submitted to a table or other database object.
• Consistent Data Manipulation: Stored Procedures execute the same code in the same order every time.
• Execution Control: You can set permissions on a Stored Procedure to allow only certain users or groups the ability to execute the command.
• Error Handling: Stored Procedures provide the opportunity to utilize consistent and efficient error handling and reporting.

Next Steps

• Work with and modify each section in this article to learn more.
• Practice and experimentation are necessary to understand how Stored Procedures can be fully used.
• Additional reading:
  • Getting Started with Stored Procedures in SQL Server
  • Creating Your Own SQL Server System Stored Procedures
  • Create a Stored Procedure (MS Docs)
  • SStored Procedures – Database Engine (MS Docs)
  • Execute a Stored Procedure (MS Docs)
  • SET NOCOUNT ON Improves SQL Server Stored Procedure Performance
  • SQL Server Stored Procedure Tutorial

About the author

Aubrey Love is a self-taught DBA with more than six years of experience designing, creating, and monitoring SQL Server databases as a DBA/Business Intelligence Specialist. Certificates include MCSA, A+, Linux+, and Google Map Tools with 40+ years in the computer industry. Aubrey first started working on PC’s when they were introduced to the public in the late 70's.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips