create procedure Restrictions

Restrictions for create procedure.

  • The maximum number of parameters that a stored procedure can have is 2048.

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

  • The maximum amount of text in a stored procedure is 16MB.

  • You cannot combine a create procedure statement with other statements in a single batch.

  • You can create a stored procedure only in the current database, although the procedure can reference objects from other databases. Most objects referenced in a procedure must exist at the time you create the procedure. However, you can include statements like drop table, create index, or truncate table. These are allowed in a create procedure statement even if the underlying object does not exist when you create the procedure.

    You can create an object within a procedure, then reference it, provided the object is created before it is referenced.

    You cannot use alter table in a procedure to add a column and then refer to that column within the procedure.

  • If you use select * in your create procedure statement, the procedure (even if you use the with recompile option to execute) does not pick up any new columns you may have added to the table. You must drop the procedure and re-create it. Otherwise, the wrong results can be caused by the insert...select statement of insert into table1 select * from table2 in the procedure when new columns have been added to both tables.

  • Within a stored procedure, you cannot create an object (including a temporary table), drop it, then create a new object with the same name. The SAP ASE server creates the objects defined in a stored procedure when the procedure is executed, not when it is compiled.

    Warning!   Certain changes to databases, such as dropping and re-creating indexes, can cause object IDs to change. When object IDs change, stored procedures recompile automatically, and can increase slightly in size. Leave some space for this increase.