Stored Procedure Rules

Here are some additional rules for creating stored procedures:

• CREATE PROCEDURE statements cannot be combined with other SQL statements in a single batch.
• The CREATE PROCEDURE definition itself can include any number and type of SQL statements, with the exception of the following CREATE statements:

• Other database objects can be created within a stored procedure. You can reference an object created in the same procedure as long as it is created before it is referenced.
• Within a stored procedure, you cannot create an object, drop it, and then create a new object with the same name.
• You can reference temporary tables within a procedure.
• If you execute a procedure that calls another procedure, the called procedure can access all objects except temporary tables created by the first procedure.
• If you create a private temporary table inside a procedure, the temporary table exists only for the purposes of the procedure; it disappears when you exit the procedure.
• 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.
• Private and public temporary stored procedures, analagous to temporary tables, can be created with the # and ## prefixes to the procedure name. # denotes a private temporary stored procedure. ## denotes a public temporary stored procedure.