Home

	Expand Search

Transact-SQL User's Guide

Chapter 14 Using Stored Procedures

Restrictions associated with stored procedures

Restrictions associated with stored procedures

Here are some additional restrictions on creating stored procedures:

• You cannot combine create procedure statements with other statements in the same batch.

• The create procedure definition itself can include any number and kind of SQL statements, except use and these create statements:

  • create view

  • create default

  • create rule

  • create trigger

  • create procedure

• You can create other database objects within a procedure. You can reference an object you created in the same procedure, as long as you create it before you reference it. The create statement for the object must come first in the actual order of the statements within the procedure.

• Within a stored procedure, you cannot create an object, drop it, and then create a new object with the same name.

• Adaptive Server creates the objects defined in a stored procedure when the procedure is executed, not when it is compiled.

• If you execute a procedure that calls another procedure, the called procedure can access objects created by the first procedure.

• You can reference temporary tables within a procedure.

• If you create a temporary table with the #prefix inside a procedure, the temporary table exists only for purposes of the procedure--it disappears when you exit the procedure. Temporary tables created using create table tempdb..tablename do not disappear unless you explicitly drop them.

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

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

Qualifying names inside procedures

Inside a stored procedure, object names used with create table and dbcc must be qualified with the object owner's name, if other users are to use the stored procedure. Object names used with other statements, like select and insert, inside a stored procedure need not be qualified because the names are resolved when the procedure is compiled.

For example, user "mary", who owns table marytab, should qualify the name of her table when it is used with select or insert if she wants other users to execute the procedure in which the table is used. The reason for this rule is that object names are resolved when the procedure is run. If marytab is not qualified, and user "john" tries to execute the procedure, Adaptive Server looks for a table called marytab owned by John.