Programming Stored Procedures

Programming Stored Procedures

Almost any Transact-SQL code that can be written as a batch can be used to create a stored procedure.
Stored Procedure Rules

Rules for programming stored procedures include:

* The CREATE PROCEDURE definition itself can include any number and type of SQL statements except for the following CREATE statements, which cannot be used anywhere within a stored procedure:

CREATE DEFAULT CREATE TRIGGER
CREATE PROCEDURE CREATE VIEW
CREATE RULE

* Other database objects can be created within a stored procedure. You can reference an object created in the same stored procedure as long as it is created before it is referenced.
* You can reference temporary tables within a stored procedure.
* If you create a private temporary table inside a stored procedure, the temporary table exists only for the purposes of the stored procedure; it disappears when you exit the stored procedure.
* If you execute a stored procedure that calls another stored procedure, 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 makes changes on a remote Microsoft® SQL Server™, those changes cannot be rolled back. Remote stored procedures do not take part in transactions.
* The maximum number of parameters in a stored procedure is 1024.
* The maximum number of local variables in a stored procedure is limited only by available memory.
* Depending on available memory, the maximum size of a stored procedure is 128 MB.