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
