Writing Stored Procedures

Introduction

The purpose of this document is to explain and illustrate how to create and use a stored procedure.

SQL Studio Management Studio (SSMS) is used for this demonstration.

A stored procedure is essentially a mini-program, consisting of a series of T-SQL statements, and it is “called” or executed by name. Stored procedures are called “procs” for short.

Finding Existing Procedures

Procs live under the Programmability folder in the SSMS Object Explorer. These are database objects that are named in the same fashion as tables, and include the schema name as a prefix. Expand the Programmability folder, and then the Stored Procedures folder, to see the list of existing procs. 

 


 

Creating a New Proc

To create a new procedure, right click on the Stored Procedures folder and select “New”. A default template for a new procedure will be opened in a new tab. The default code for a new proc is a bit intimidating. Most of the supplied content and sample code is not needed. All the areas in gray are suggestions, but not necessary.

 


 

Here is the same code to create a new proc, with all the miscellaneous and unnecessary comments and code stripped out:

The first thing to notice is the comment section at the top. Every proc should have a comment section like this that describes the author, date created (or revised) and a description.

Next is the CREATE PROCEDURE line. This defines the name for the proc, which should include the schema name. As with tables, it is best to use normal words to name a proc, without spaces. Some organizations will define a standard to prefix proc names by what they do, such as list, insert, update, etc. The “lst_” prefix for this proc is a prefix to indicate that it will return a dataset from a SELECT statement.

The above proc is very simple and consists of a single SELECT statement. as you can see from the table column list on the left, this select statement is returning all columns in the table.

When creating stored procedures, the use of the asterisk (*) should be avoided in SELECT statements, and a specific list of columns should be defined. (Remember that it is easy to start with a list of all columns by clicking on the column folder and dragging it into the coding space.)

A WHERE clause is not included in this example, so all rows in the table will be returned. There is an ORDER BY clause to sort the returned records.

To create the new procedure, execute the code in the Query Manager tab. It is not necessary to highlight the code, since there should not be any other code in that tab (but you may highlight the code if you wish).


 

Running a Stored Procedure

Once a procedure is created, it can be executed when needed. To call or execute a stored procedure, the proc name is used with the EXEC command.

For a stored procedure that executes a SELECT statement, the result of executing the proc is the same as executing that SELECT statement. It returns a table such as the one above. (We refer to this type of dataset as a table, even though it is not a physical table in the database.)

This is the same result that would be returned if we ran the SELECT statement by itself. The difference is that we have packaged a set of code into a proc and execute the proc by name. This is advantageous in many ways. For example, a front-end or middleware program can request this data simply by supplying the proc name; it does need to know anything about the underlying table(s) or the T-SQL code needed to query the data.


 

Modifying a Procedure

It is not possible to run CREATE PROCEDURE for a proc name that already exists. This returns an error.

There are two ways to modify or alter a stored procedure. The first method is to right-click the proc name in the Stored Procedures folder and select “Modify”. (Remember that Object Explorer does not refresh automatically. After creating a new proc, right-click on the Stored Procedures folder and select “Refresh”.)


 

This will open the proc in a new tab in “Alter” mode, where the ALTER PROCEDURE statement is present instead of CREATE PROCEDURE.

Notice some statements are added above the comment area; you can ignore that code for now.

The other method to modify a proc is related to whether the tab has been closed where the original CREATE PROCEDURE code was created. If not, just change “CREATE” to “ALTER” and you are at the same place.

When modifying the procedure, any changes you make will be saved when the procedure is updated (it is actually dropped and recreated).

To update the proc, execute all of the ALTER PROCEDURE code in the Query Manager tab.


 

Errors in Procedure Code

The Query Manager will not save a proc with obvious errors. As with the proc code below, it will try to identity errors with red squiggly lines. When the proc definition is executed to save and create the procedure, the process will fail and any error messages will be displayed in the Messages area.

Correct errors and execute the code again to update the procedure.

Using Parameters

Stored procedures may also contain parameters. This allows the proc to be called and executed with different WHERE filters, for example. Parameters are defined like variables, using the "at" prefix ( @), and by assigning a data type. Multiple parameters can be defined, separated by commas. Parameters are defined at the top of a stored procedure, before the AS and BEGIN keywords. Multiple parameters are separated by commas, and the DECLARE keyword is not needed.

Executing a stored procedure with a parameter is accomplished in the same fashion as before. Parameters can be provided by position (first, second, etc.) or by name. Best Practice is to always call a proc using named parameters. This means the parameters do not need to be in the same order as they are declared in the proc. If the proc changes (such as a parameter is inserted in the list), the EXEC statement will still work as before.

Parameters are one of the powerful features of stored procedures. It allows the same query to be executed with different options – without rewriting any code:

 


 

Using Procedures for Other Tasks

Stored procedures can execute almost any T-SQL code that normally run in the Query Manager. For example, a proc can be used for inserting or updating records. Consider the example of adding a new record to a Pets table. The table definition is displayed in the left panel.

In this case, the proc has five parameters defined. Because it is an insert statement, the petid column will not be included in the INSERT statement, and is not needed as a parameter.

The proc name includes a prefix on “ins” to indicate that this proc is used to insert a record. Best practice in SQL Server is that separate procs should be used for operations to insert, update and delete records.

Each parameter is defined with a data type and the name is prefixed with the “ ” symbol. Commas are used to separate the multiple parameters.

Parameters can be defined with a default value, by using an equal sign and providing the appropriate value (string, number, etc.).

When a default value is present, the parameter value is not required. This means the EXEC statement that calls the proc may omit that parameter. Parameters without a default are required. A value must be supplied for those parameters.


 

Here is a set of procedure calls illustrating how to execute a procedure with parameters.

 

The first example above (line 3) calls the proc with values for the five parameters – but without using the parameter names. While this is possible, it is never recommended. If the proc changes in some way, this statement would fail.

The second example (line 7), makes the same call but provides the name for each parameter. This is always the preferred syntax. Note that the parameters are not in the same order as in the proc definition. When named parameters are used, the positions do not matter. It is not possible to use both named and unnamed parameter values. Named parameters should always be used.

The last example above calls the proc with only the required parameters. Both “age” and “weight” have defaults, and are not required parameters. As seen by the result (petid=7), the default values were inserted for both age and weight.


 

 

Calling a proc without supplying a required parameter results in an error. The T-SQL insert statement is aborted and nothing is inserted in the table:


 

Updating a Record

This is an example of an update proc for the same Pets table. The procedure code is very similar to the insert example. One difference is the inclusion of the @petid parameter. Because the petid column is the primary key, it is the main column used to find and update records in this table.

The update statement is a standard T_SQL statement. All of the values come from the proc parameters, including the @petid parameter value used in the where clause. As with an update statement, if the value supplied for @petid is not an existing record id, the statement will simply not update any records. This would not cause an “exception”.


 

Here is an example of running the update proc:

One major thing to notice: values must be provided for all parameters, if you want those values updated accordingly. SQL Server does not know what values you want updated and which ones you do not. So it updates all columns according to the update statement.

It would be possible to create the proc with fewer parameters and update only selected columns, but the update statement will execute as written and update all columns specified. It is still possible to use defaults, but it makes less sense in this case because the default might overwrite existing values.

Normally, front-end and middleware programs will submit all values for an update statement. Although the user may only modify a single field value, the update process updates all values in the record; this changes modified values and normally leaves other values as they are.

One other thing to note. SQL Server is normally case-insensitive. T-SQL inserts and updates column values exactly as provided. It would not be uncommon, for example, for a front-end application to capitalize certain values (such as a name), so that the data is, in fact, modified from its original state.

Retrieving the ID of a New Record

This is an advanced topic, but it is often a requirement for applications inserting records to know the PK of a new record. Consider a web form where a new Pet is being entered. Once it is saved, the web app wants to redisplay the same form with the new ID. Why? If the user were to click the <Save> button multiple times, they would create the same new Pet multiple times. By retrieving the record ID (petid) and including it in the form, any subsequent saves would only update the existing record – not create a new record.

So, this is a common requirement. Here is a second insert proc to illustrate how this is done.

There are only three additional lines of code from the original sample. Line 25 declare a variable to hold the petid for the new record. Because petid is an identity column, SQL Server will insert the value automatically. The task is to retrieve that value after the record has been created.

A new record is created when line 28 is executed. The new record and the PK value is available immediately.

Line 32 uses the ident_current() function to retrieve the most recent petid for this table. There are several similar functions. This one is special, however. It retrieves the last identity key created for this table by this session – meaning this specific stored procedure instance. So, even if 10 other users were all creating pets at exactly the same time, using this same procedure, it would retrieve only the PK created by this user and this one call of the proc.


 

Here is the call to the new proc and the results:

Notice that the value of the new PK (9) was returned by the proc.

This proc was different because it executed multiple T-SQL statements. It executed an UPDATE statement and executed a SELECT.

There are multiple ways to return values such as the new PK to a calling program. Using a SELECT is one method. The appropriate method would depend on how the calling program is written. If it is looking for the result of a SELECT statement, this approach works fine.