SQL Server 2016: Create a Stored Procedure

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.

Programmability

But 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:

  • Accept input parameters (and return multiple values in the form of output parameters to the calling program).
  • Contain programming statements.
  • Return a status value to a calling program to indicate success or failure, and the reason for any failure.

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:

BenefitExplanation
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.

CREATE PROCEDURE myStoredProcedure AS
...

OR

CREATE PROCEDURE myStoredProcedure @ParameterName DataType} AS
...

We'll now create a stored procedure based on one of the views that we created earlier — the RecentAlbums view.

This view returns all albums released in the last 20 years. This is fine as long as it will only ever need to look back 20 years. But what if you want the user to choose how many years it should cover?

A stored procedure can solve this problem.

We will create a stored procedure that accepts a parameter. The value of the parameter will be the number of years to search back over. Therefore, this value can be specified by the user whenever they execute the stored procedure.

  1. Design the Stored Procedure

    Screenshot of creating a stored procedure in SQL Server.

    Open a new query window and add the code for the stored procedure.

    In our case, we will copy/paste the code from the RecentArtists view and modify the top part, so that it becomes a stored procedure.

    We will add a parameter called @Count that will determine how many years the stored procedure should look back.

    So we will replace the hardcoded value of 20 with @Count

    Sample Code

    Here's the code from our example:

    CREATE PROCEDURE spRecentAlbums @Count int
    AS
    SELECT  Albums.ReleaseDate, 
            Albums.AlbumName, 
            Genres.Genre, 
            Artists.ArtistName,
            Artists.ActiveFrom
    FROM Albums 
    INNER JOIN
            Artists ON 
            Albums.ArtistId = Artists.ArtistId 
            INNER JOIN
                Genres ON 
                Albums.GenreId = Genres.GenreId
    WHERE   (Albums.ReleaseDate > DATEADD(year, - @Count, GETDATE()));
  2. Create the Stored Procedure

    Screenshot of creating a stored procedure in SQL Server.

    Once it all looks good, you can execute the statement to create the stored procedure.

    Click Execute to create the stored procedure.

    Once the stored procedure has been created, you can see it in the Object Explorer (you might need to refresh the Stored Procedures node first).

  3. Execute the Stored Procedure

    Screenshot of executing a stored procedure in SQL Server.

    Now that it has been created, you can execute the stored procedure.

    Open a new query window, add this:

    EXEC spRecentAlbums @Count = 5;

    Now click Execute from the toolbar.

    The stored procedure will return all albums released in the last 5 years.

  4. Try different Parameters

    Screenshot of executing a stored procedure in SQL Server.

    Try changing the value of the parameter to see how this affects the results.

    You can also run multiple statements one after the other. A new results pane will appear for each statement.

Screenshot of creating a stored procedure via the GUI.

Stored Procedure Template

In SQL Server 2016, you can create a stored procedure by right-clicking on the Stored Procedures node in the Object Explorer and selecting New > Stored Procedure... or New > Natively Compiled Stored Procedure....

This will open a template that's ready to be populated with your own specific procedure.

Execute a Stored Procedure via the GUI

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

  1. Launch the Execute Procedure Dialog Box

    Screenshot of executing a stored procedure via the GUI.

    In the Object Explorer, right-click on the stored procedure and select Execute Stored Procedure....

  2. Supply Parameters

    Screenshot of executing a stored procedure via the GUI.

    Enter a value for any parameters that the stored procedure requires, then click OK.

  3. The Results

    Screenshot of executing a stored procedure via the GUI.

    The results are displayed.

Modify a Stored Procedure

If you need to modify an existing stored procedure, simply replace CREATE with ALTER (along with the updated procedure).

This example modifies the stored procedure so that the results are sorted by release date in descending order:

ALTER PROCEDURE spRecentAlbums @Count int
AS
SELECT  Albums.ReleaseDate, 
        Albums.AlbumName, 
        Genres.Genre, 
        Artists.ArtistName,
        Artists.ActiveFrom
FROM Albums 
INNER JOIN
        Artists ON 
        Albums.ArtistId = Artists.ArtistId 
        INNER JOIN
            Genres ON 
            Albums.GenreId = Genres.GenreId
WHERE   (Albums.ReleaseDate > DATEADD(year, - @Count, GETDATE()))
ORDER BY Albums.ReleaseDate DESC;

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:

  • Configure security accounts
  • Set up linked servers
  • Create a database maintenance plan
  • Create full text search catalogs
  • Add remote login
  • Configure replication
  • Set up scheduled jobs
  • and much more...

System stored procedures are prefixed with sp_, so it's best to avoid using that prefix for your own procedures.

Naming Conventions

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_ (to indicate a user-defined stored procedure), 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, recent_albums), while others will use title case (eg, RecentAlbums).

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

  • RecentAlbums
  • recent_albums
  • uspRecentAlbums
  • usp_recent_albums
  • selectRecentAlbums
  • select_RecentAlbums
  • select_recent_albums
  • getRecentAlbums
  • get_recent_albums

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_RecentAlbums or uspRecentAlbums.

As mentioned, avoid using sp_ as the prefix for your stored procedure names. SQL Server uses this prefix for the system stored procedures.

SQL Server searches the system stored procedures first, so at best, you'll take a performance hit. At worst, your procedure won't run (if it shares a name with a system stored procedure).