The Nuances of Stored Procedures

Stored procedure code can vary from the most simple of DML statements to the most complex queries using flow-control, joins, calculations, and so on. However, the following nuances are important to keep in mind:

• Stored procedure names, like triggers, are stored in sysobjects and the code is stored in syscomments. To inspect the stored procedure code, execute sp_helptext in the parent database of the stored procedure. More on sp_helptext later.

• To check which objects are referenced by a stored procedure, execute sp_depends.

• The words PROCEDURE and PROC can be used interchangeably, and SQL Server recognizes both. The statements CREATE PROC, DROP PROC, and ALTER PROC are thus also valid.

• The following CREATE statements cannot be used in a stored procedure: CREATE DEFAULT, CREATE PROCEDURE, CREATE TRIGGER, CREATE RULE, CREATE VIEW.

• You can create any other database object from a stored procedure and even reference it in the stored procedure, as long as you create it before you reference it. You can even reference temporary tables in a stored procedure.

• The maximum size of a stored procedure is 128 MB.

• The number of local variables in a stored procedure is limited by available memory.

• The maximum number of parameters in a stored procedure is 1,024.

• You cannot use remote stored procedures in remote transaction scenarios. If you execute a remote stored procedure, the transaction on the remote instance cannot be rolled back.

• Stored procedures can spawn stored procedures that can access any object created by the parent stored procedure. However, if you create a local temporary table, it only exists for the stored procedure that created it. If you exit the stored procedure, the temporary table is lost.