Create a Stored Procedure in SQL Server 2017

• Create a View
• Backup a Database

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

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:

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.

Example

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.

   CREATE PROCEDURE spAlbumsFromArtist @ArtistName varchar(255) AS SELECT AlbumName, ReleaseDate FROM Albums INNER JOIN Artists ON Albums.ArtistId = Artists.ArtistId WHERE Artists.ArtistName = @ArtistName; GO

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.

   

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.

   EXECUTE spAlbumsFromArtist @ArtistName = "Devin Townsend";

   

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.

   ALTER PROCEDURE spAlbumsFromArtist @ArtistName varchar(255) AS SELECT al.AlbumName, al.ReleaseDate, g.Genre FROM Albums al INNER JOIN Artists ar ON al.ArtistId = ar.ArtistId INNER JOIN Genres g ON g.GenreId = al.GenreId WHERE ar.ArtistName = @ArtistName; GO

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.

   EXECUTE spAlbumsFromArtist @ArtistName = "Devin Townsend";

   

• Create a View
• Backup a Database