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.
