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

TRIGGERS, TRIGGERS PERFORMANCE AND RULES FOR CREATING TRIGGERS

TRIGGERS, TRIGGERS PERFORMANCE AND RULES FOR CREATING TRIGGERS IN T-SQL

Triggers:

A Trigger is a special type of stored procedure that’s automatically invoked when the user try to modify data that it’s designed to protect. Triggers help to secure data’s integrity  by preventing unauthorized or inconsistent changes from being made.

Triggers Performance:

From the performance point-of-view, triggers have a relatively low amount of overhead. Most of the time involved in running a trigger is used up by referencing other tables. The referencing can be fast if the other tables are in memory or a bit slower if they read from disk.

Rules for creating Triggers:

i)                    Triggers can’t be created on temporary tables. They can, however, reference views and temporary tables.

ii)                  Triggers can’t return result sets. Using the IF EXISTS clause as a part of a SELECT Statement in trigger code is a common practice.

iii)                Triggers should be used to maintain data integrity, maintain referential integrity, and encapsulate business rules.

iv)                Triggers can be encrypted in the syscomments table if you specify the WITH ENCRYPTION option.

v)                  WRITETEXT statement don’t activate triggers. They are used to modify text or image data, and it’s a nonlogged transaction.

vi)                The following SQL Statement can’t be used in a trigger:

Ø  All CREATE Statements

Ø  All DROP Statements

Ø  ALTER TABLE and ALTER DATABASE

Ø  TRUNCATE TABLE

Ø  RECONFIGURE

Ø  LOAD DATABASE or TRANSACTION

Ø  UPDATE  STATISTICS

Ø  SELECT INTO

Ø  DISK STATEMENT