STORED PROCEDURE AND RULES FOR CREATING STORED PROCEDURE

STORED PROCEDURE AND RULES FOR CREATING STORED PROCEDURE IN T-SQL  

Stored Procedure:

To create Stored procedure, the CREATE PROCEDURE Statement is used. Stored procedures are created in the current database unless a temporary stored procedure is created in tempdb. Because stored procedure are precompiled, they usually provide the best performance of any type of query.

Rules for creating Stored procedure:

i)                    The name must follow the rules for identifiers.

ii)                  Referenced objects must exist when your stored procedure runs.

iii)                Object with the same name in a single stored procedure can’t be created and then drop or re-create.

iv)                Temporary tables can be referenced within the stored procedure. Local temporary tables disappear when the procedure ends.

v)                  Upto 1024 parameters can be allowed.

vi)                Procedure can be nested within procedure upto 32 levels deep.

vii)              Stored Procedure can’t have the following T-SQL create statement in them:

Ø  CREATE DEFAULT

Ø  CREATE RULE

Ø  CREATE PROCEDURE

Ø  CREATE TRIGGER

Ø  CREATE VIEW