Tuesday, March 31, 2009

STORED PROCEDURE : Very IMP For SQL Lovers

Defination

A stored procedure is a set of SQL commands that has been compiled and stored on the database server.

Once the stored procedure has been "stored", client applications can execute the stored procedure over and over again without sending it to the database server again and without compiling it again.

Stored procedures improve performance by reducing network traffic and CPU load

Some Details

Typical uses for stored procedures include data validation (integrated into the database) or access control mechanisms. Furthermore, stored procedures are used to consolidate and centralize logic that was originally implemented in applications. Large or complex processing that might require the execution of several SQL statements is moved into stored procedures and all applications call the procedures only.

Stored procedures are similar to user-defined functions (UDFs). The major difference is that UDFs can be used like any other expression within SQL statements, whereas stored procedures must be invoked using the CALL statement

CALL procedure(…)

or

EXECUTE procedure(…)

Stored procedures can return result sets, i.e. the results of a SELECT statement. Such result sets can be processed using cursors by other stored procedures by associating a result set locator, or by applications. Stored procedures may also contain declared variables for processing data and cursors that allow it to loop through multiple rows in a table. The standard Structured Query Language provides IF, WHILE, LOOP, REPEAT, CASE statements, and more. Stored procedures can receive variables, return results or modify variables and return them, depending on how and where the variable is declared

Difference between Stored Procedures and Functions

a. Only function can Return a value (to be precise using the Return keyword)

b. Procedures can use ‘Return’ keyword but without any value being passed

c. Functions could be used in select statements, provided they don’t do any data manipulation inside and also should not have any OUT, IN OUT parameters

Comparison with Dynamic SQL

Because stored procedure statements are stored directly in the database, this can remove the compilation overhead that is typically required in situations where software applications send inline (dynamic) SQL queries to a database. However, most database systems implement statement caches to avoid repetitive compilation of dynamic SQL statements. In addition, pre-compiled SQL statements, while avoiding some overhead, add to the complexity of creating an optimal execution plan because not all arguments of the SQL statement are supplied at compile time. Depending on the specific database implementation and configuration, mixed performance results will be seen from stored procedures versus generic queries or user defined functions.

No comments:

Post a Comment