• Article

Designing Stored Procedures (Database Engine)

Almost any Transact-SQL code that can be written as a batch can be used to create a stored procedure.

Rules for Designing Stored Procedures

Rules for designing stored procedures include the following:

  • The CREATE PROCEDURE definition itself can include any number and type of SQL statements, except for the following statements. These cannot be used anywhere within a stored procedure.

    CREATE AGGREGATE

    CREATE RULE

    CREATE DEFAULT

    CREATE SCHEMA

    CREATE or ALTER FUNCTION

    CREATE or ALTER TRIGGER

    CREATE or ALTER PROCEDURE

    CREATE or ALTER VIEW

    SET PARSEONLY

    SET SHOWPLAN_ALL

    SET SHOWPLAN_TEXT

    SET SHOWPLAN_XML

    USE database_name

     

  • Other database objects can be created within a stored procedure. You can reference an object created in the same stored procedure as long as it is created before it is referenced.

  • You can reference temporary tables within a stored procedure.

  • If you create a local temporary table inside a stored procedure, the temporary table exists only for the purposes of the stored procedure; it disappears when you exit the stored procedure.

  • If you execute a stored procedure that calls another stored procedure, the called stored procedure can access all objects created by the first stored procedure, including temporary tables.

  • If you execute a remote stored procedure that makes changes on a remote instance of Microsoft SQL Server, those changes cannot be rolled back. Remote stored procedures do not take part in transactions.

  • The maximum number of parameters in a stored procedure is 2100.

  • The maximum number of local variables in a stored procedure is limited only by available memory.

  • Depending on available memory, the maximum size of a stored procedure is 128 megabytes (MB).

Qualifying Names Inside Stored Procedures

Inside a stored procedure, object names used with statements (for example, SELECT or INSERT) that are not schema-qualified default to the schema of the stored procedure. If a user who creates a stored procedure does not qualify the name of the tables or views referenced in SELECT, INSERT, UPDATE, or DELETE statements within the stored procedure, access to those tables through the stored procedure is restricted by default to the creator of the procedure.

Object names used with all Data Definition Language (DDL) statements such as CREATE, ALTER, or DROP statements, DBCC statements, EXECUTE and dynamic SQL statements should be qualified with the name of the object schema if other users are to use the stored procedure. Specifying the schema name for these objects ensures the name resolves to the same object regardless who the caller of the stored procedure is. If a schema name is not specified, SQL Server will attempt to resolve the object name first using the default schema of the caller or the user specified in the EXECUTE AS clause and then the dbo schema.

Obfuscating Procedure Definitions

To convert the original text of the CREATE PROCEDURE statement to an obfuscated format, use the WITH ENCRYPTION option. The output of the obfuscation is not directly visible in any of the system tables or views in SQL Server 2008: users without access to system tables, system views, or database files cannot retrieve the obfuscated text. However, the text is available to privileged users with direct access to database files. These users may be able to reverse engineer the obfuscation to retrieve the original text of the stored procedure definition.

SET Statement Options

The Database Engine saves the settings of both SET QUOTED_IDENTIFIER and SET ANSI_NULLS when a Transact-SQL stored procedure is created or altered. These original settings are used when the stored procedure is executed. Therefore, any client session settings for SET QUOTED_IDENTIFIER and SET ANSI_NULLS are ignored during stored procedure execution. SET QUOTED_IDENTIFIER and SET ANSI_NULLS statements that occur within the stored procedure do not affect the functionality of the stored procedure.

Other SET options, such as SET ARITHABORT, SET ANSI_WARNINGS, or SET ANSI_PADDINGS are not saved when a stored procedure is created or altered. If the logic of the stored procedure is dependent on a particular setting, include a SET statement at the start of the procedure to ensure the proper setting. When a SET statement is executed from a stored procedure, the setting remains in effect only until the stored procedure completes. The setting is then restored to the value it had when the stored procedure was called. This allows individual clients to set the options wanted without affecting the logic of the stored procedure.

Note

ANSI_WARNINGS is not honored when passing parameters in a stored procedure, user-defined function, or when declaring and setting variables in a batch statement. For example, if a variable is defined as char(3), and then set to a value larger than three characters, the data is truncated to the defined size and the INSERT or UPDATE statement succeeds.