How to Create a Stored Procedure

Below is the syntax of the CREATE PROC statement you use to create a stored procedure. You code the name of the procedure in the CREATE PROC clause. Note that stored procedure names can’t be the same as the name of any other object in the database. To help distinguish a stored procedure from other database objects, it’s a good practice to prefix its name with the letters sp.

When the CREATE PROC statement is executed, the syntax of the SQL statements within the procedure is checked. If you’ve made a coding error, the system responds with an appropriate message and the procedure isn’t created.

Because the stored procedure is created in the current database, you need to change the database context by coding a USE statement before the CREATE PROC statement. In addition, CREATE PROC must be the first and only statement in the batch. Since the script in this section creates the procedure after a USE and DROP PROC statement, for example, it has a GO command just before the CREATE PROC statement.

In addition to stored procedures that are stored in the current database, you can create temporary stored procedures that are stored in the tempdb database. These procedures exist only while the current database session is open, so they aren’t used often. To identify a temporary stored procedure, prefix the name with one number sign (#) for a local procedure and two number signs (##) for a global procedure.

After the name of the procedure, you code declarations for any parameters it uses. You’ll learn more about that in the sections that follow.

You can also code the optional WITH clause with the RECOMPILE option, the ENCRYPTION option, the EXECUTE_AS_clause option, or any combination of these options. The RECOMPILE option prevents the system from precompiling the procedure. That means that the execution plan for the procedure must be compiled each time it’s executed, which will slow down most procedures. For this reason, you should generally omit this option.

Some procedures, however, might make use of unusual or atypical values. If so, the first compilation may result in an execution plan that isn’t efficient for subsequent executions. In that case, the additional overhead involved in recompiling the procedure may be offset by the reduced query execution time. If you find that a stored procedure you’ve written performs erratically, you may want to try this option.

ENCRYPTION is a security option that prevents the user from being able to view the declaration of a stored procedure. Since the system stores the procedure as an object in the database, it also stores the code for the procedure. If this code contains information that you don’t want the user to examine, you should use this option.

The EXECUTE_AS_clause option allows you to specify an EXECUTE AS clause to allow users to execute the stored procedure with a specified security context. For example, you can use this clause to allow users to execute the stored procedure with the same security permissions as you. That way, you can be sure that the stored procedure will work for the caller even if the caller doesn’t have permissions to access all of the objects that you used within the stored procedure.

The syntax of the CREATE PROC statement

 

A script that creates a stored procedure that copies a table

 

Description

  • You use the CREATE PROC statement to create a stored procedure in the current database. The name of a stored procedure can be up to 128 characters and is typically prefixed with the letters sp.
  • The CREATE PROC statement must be the first and only statement in a batch. If you’re creating the procedure within a script, then, you must code a GO command following any statements that precede the CREATE PROC statement.
  • To create a temporary stored procedure, prefix the procedure name with a number sign (#) for a local procedure or two number signs (##) for a global procedure. A temporary stored procedure only exists while the current database session is open.
  • You can use parameters to pass one or more values from the calling program to the stored procedure or from the procedure to the calling program. See figures “How to declare and work with parameters” and “How to call procedures with parameters” for more information on working with parameters.
  • The AS clause contains the SQL statements to be executed by the stored procedure. Since a stored procedure must consist of a single batch, a GO command is interpreted as the end of the CREATE PROC statement.
  • The RECOMPILE option prevents the system from precompiling the procedure, which means that it has to be compiled each time it’s run. Since that reduces system performance, you don’t typically use this option.
  • The ENCRYPTION option prevents users from viewing the code in a stored procedure. See section “How to work with system stored procedures” for more information on viewing stored procedures.
  • The EXECUTE_AS_clause option was introduced with SQL Server 2005. You can use this option to allow users to execute the stored procedure with the permissions specified by the EXECUTE AS clause. For more information, look up “EXECUTE AS clause” in Books Online.

Chapter: Code Stored Procedures, Functions, and Triggers