SQL Server 2014 - Stored Procedures

Stored procedures can improve security and performance, as well as increase productivity by facilitating modular development.

In SQL Server, stored procedures are much more powerful than views. Views allow us to run a complex query by simply doing a SELECT against the view itself, which by the way, is a pretty cool thing. But views don't really provide us with the ability to provide business logic within our code. For example, views don't allow us to add conditions to the code (such as IF statements). Fortunately, that's where stored procedures come in.

What Is A Stored Procedure?

A stored procedure is a group of SQL statements compiled into one. Similar to what I was saying on the SQL scripts page, where I mentioned that you could run many SQL scripts as one.

However, a stored procedure is more than just a long script. It's a script that has been saved in SQL Server specifically under the Stored Procedures node. A stored procedure will typically contain some business logic. For example, a stored procedure can accept parameters that are passed to it and test against those parameters using IF statements. Eg, if the parameter is one value, do this, if it's another value, do that.

Their ability to contain business logic makes stored procedures a powerful part of SQL Server. Stored procedures can improve performance in an application, because the stored procedure is parsed and optimized as soon as it's created, and then stored in memory. Running a conditional query via stored procedure can be extremely quick - compared to an application that sends a query across the network, to the SQL Server, then has all the data returned to it across the network so it can filter through it, and pick out only the records it's interested in.

Benefits of Stored Procedures

Here are some of the main benefits in using stored procedures:

Modular programmingYou can write a stored procedure once, then call it again and again, from different parts of an application (and even from multiple applications).
PerformanceStored procedures provide faster code execution and reduce network traffic.
  • Faster execution: Stored procedures are parsed and optimized as soon as they are created and the stored procedure is stored in memory. This means that it will execute a lot faster than sending many lines of SQL code from your application to the SQL Server. Doing that requires SQL Server to compile and optimze your SQL code every time it runs.
  • Reduced network traffic: If you send many lines of SQL code over the network to your SQL Server, this will impact on network performance. This is especially true if you have hundreds of lines of SQL code and/or you have lots of activity on your application. Running the code on the SQL Server (as a stored procedure) eliminates the need to send this code over the network. The only network traffic will be the parameters supplied and the results of any query.
SecurityUsers can execute a stored procedure without needing to execute any of the statements directly. Therefore, a stored procedure can provide advanced database functionality for users who wouldn't normally have access to these tasks, but this functionality is made available in a tightly controlled way.

Create a Stored Procedure

To create a stored procedure, you need to use the CREATE PROCEDURE statement, followed by the code that makes up the stored procedure. If your stored procedure is going to accept parameters, they need to be included after the name.


The following code creates a stored procedure called "LatestTasks". It accepts a parameter called @Count. Whenever you call this stored procedure, you pass the @Count parameter along with a number, which determines how many rows you want returned. Here's the code:

Run this code in the SQL Server Management Studio and you'll see that it gets created under the Stored Procedures node as "LatestTasks".

Screenshot of stored procedure in the Object Explorer

In SQL Server 2014, you can create a stored procedure by right-clicking on the Stored Procedures node/folder and selecting Stored Procedure.... This will open a template that's ready to be populated with your own specific procedure.

Execute a Stored Procedure

Now that you've created your stored procedure, any time you want to execute it, you need to call it using either EXECUTE or EXEC. If the stored procedure requires parameters you provide those after the procedure name. Like this:


In the following example, we execute the stored procedure twice at the same time. The first time we call it, we pass in a @Count of 3. The second time we pass in a value of 5.

The screenshot shows that by passing the paramater (and a value), the stored procedure returns results based on the value that we provide. The top result set returns 3 rows because we passed in a value of 3. The second result set returns 5 rows because we provided a value of 5:

Screenshot of stored procedure being executed

Using The GUI

You can also use the graphical user interface to execute a stored procedure.

Here's how:

  1. Using the Object Explorer, navigate to the stored procedure
  2. Right click on the stored procedure and select Execute Stored Procedure...:
    Initiating the execution of a stored procedure
  3. A dialog will appear. Enter your chosen parameter values:
    Initiating the execution of a stored procedure
  4. Click OK
  5. SQL Server will now generate the SQL code and execute the stored procedure.

Modifying a Stored Procedure

If you need to modify an existing stored procedure, simply replace the CREATE with ALTER. Let's add a space in between "Latest" and "Tasks" (i.e. make it "Latest Tasks") and add the Description field:

System Stored Procedures

SQL Server includes a large number of system stored procedures to assist in database administration tasks. Many of the tasks you can perform via the GUI can be done via a system stored procedure. For example, some of the things you can do with system stored procedures include:

Naming Conventions

Expand the System Stored Procedures node and take a look. You'll notice that their names all begin with sp_ to indicate that it's a stored procedure. The system stored procedures obviously follow a naming convention.

It is a good idea to develop a consistent naming convention for your stored procedures (and for all other objects in your database).

Some people prefix their stored procedures with usp_, others begin it with a SQL keyword such as SELECT, INSERT, UPDATE, DELETE. Others use an abbreviation for the application.

Some use underscores to separate each word in the stored procedure (eg, latest_tasks), while others will use title case (eg, LatestTasks).

Therefore, it is possible that our stored procedure could been named any of the following, depening on the naming convention being used.

You get the picture. The important thing is consistency. Choose one and stick with it. It will make it easier when you need to use a stored procedure. Imagine having scores, or even hundreds of stored procedures, and every time you go to execute one, you need to navigate to it in the Object Explorer purely because you can't remember whether you called it usp_LatestTasks or uspLatestTasks.

So that's stored procedures covered. Next we've got user logins.