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:
- 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:
|Modular programming||You can write a stored procedure once, then call it again and again, from different parts of an application (and even from multiple applications).|
|Performance||Stored procedures provide faster code execution and reduce network traffic.
|Security||Users 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.
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.
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.
Execute the Stored Procedure
Now that the stored procedure has been created, you can run it by using a
EXECUTEstatement and passing any required parameters.
In our example, we need to pass the artist's name.
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.
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.
Execute the Stored Procedure
Now that we've added the
Genrecolumn to the view, the stored procedure now returns that column when we execute it.