Stored Procedure Design Rules
The stored procedure design rules include:
- The create procedure statement can include any number of SQL statements and types, except the following statements.
These statements cannot be used anywhere in the 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 |
UseDatabase_name |
|
- Other database objects can be created in the stored procedure. You can reference an object created in the same stored procedure as long as it has been created at the time of reference.
- You can reference a temporary table in a stored procedure.
- If you create a local temporary table in the stored procedure, the temporary table exists only for the stored procedure. After you exit the stored procedure, the temporary table disappears.
- If another stored procedure is called, 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 changes the remote Microsoft SQL server instance, you cannot roll back these changes. Remote stored procedures are not involved in transaction processing.
- The maximum number of parameters in stored procedures is 2100.
- The maximum number of local variables in a stored procedure is limited by the available memory.
- Depending on the available memory, the maximum storage process is 128 MB.
