Create a Stored Procedure in SQL Server 2017

A stored procedure is a group of SQL statements compiled into one. Stored procedures can include business logic and other programming constructs.

In SQL Server, a stored procedure is a group of one or more Transact-SQL statements or a reference to a Microsoft .NET Framework common runtime language (CLR) method.


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, and it can:

Stored procedures often contain 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.

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.

How to Create a Stored Procedure

To create a stored procedure, 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.


Here's an example of creating a stored procedure and then executing that stored procedure.

  1. Create the Stored Procedure

    Run the following statement against the Music database that we've created throughout this tutorial.

    This example creates a stored procedure called AlbumsFromArtist. It selects all albums from an artist that is specified when the stored procedure runs.

  2. View the Stored Procedure

    Expand the Programmability > Stored Procedures node under the applicable database to see your newly created stored procedure. You can also expand the stored procedure's Parameters node to see the parameters you need to pass when you run it.

    Screenshot of the highlighted stored procedure with the code that created it also highlighted
  3. Execute the Stored Procedure

    Now that the stored procedure has been created, you can run it by using a EXECUTE statement and passing any required parameters.

    In our example, we need to pass the artist's name.

    Screenshot of running the stored procedure

Alter a Stored Procedure

You can modify your stored procedure with the ALTER PROCEDURE statement. Just like when you alter a view, you can alter a stored procedure by simply applying the new stored procedure definition.

  1. Alter the Stored Procedure

    Run the following statement. This adds a new column to the results returned by the stored procedure. We also add aliases to the column names.

  2. Execute the Stored Procedure

    Now that we've added the Genre column to the view, the stored procedure now returns that column when we execute it.

    Screenshot of running the stored procedure