Stored Procedures - Advantages

Advantages

Getting Data you can mask the underlying data structure from the application via procedure and isolate the storage from the presentation layer. Users can be granted permission to run only those procedures which they need access.

Modular programming procedures give the ability to write modular code. Once you have created the new procedure, you can easily replace the redundant code with a call to the new procedure. It can greatly reduce the amount actual sql code, because you can call other stored procedure to perform like functions.

                                                          Once a procedure is written and tested, you can call that procedure from many places. A library of stored procedures can be developed once and reused many times by many different applications.

Reduced client processing stored procedures can reduce the amount of client-side processing by looking up data and maintaining key values and internal integrity. Server based stored procedures allow you to develop thin client applications-applications concerned only with displaying data in a manner that meets the user’s needs. Very little data logic is needed in client application with strong server-side processing.

Network Traffic by using stored procedures in dial-in modem access; client/server remote applications have attained speeds almost equal to regular network applications.

Statement-level Recompilation Sql server can reuse existing plan to execute a procedure. While performing actions regarding indexes, query plan should be optimized by recompiling the stored procedures. Sql server 2005 introduces the statement-level recompilation, in which only the statements that caused recompilation is compiled instead of the entire procedure.

Object Reference stored procedures also enhances performance with regard to object references. With stored procedures, the objects are checked when compiled and stored in the query plan. Each subsequent call to the procedure does not trigger the object reference check on the server, thus reducing overhead.

Parameters the maximum number of parameters you can use with a stored procedure is 2100. You stay away from using variant parameters since they need type casting. Output parameters can be used in complex client applications that require multiple return values from a stored procedure as well as the result sets.

Variables you can create as many variables as needed within your stored procedures. Here is a list of few important global variables.

@@connections – contains the number of logins since the server was last started.

@@Error – holds the return code or status of the last sql statement (0 means success)

@@fetch_status – contains the result of a cursor’s fetch command

@@identity – holds the value of the last identity value generated by an insert statement

@@nestlevel – holds the current count of nesting levels within your stored procedure

@@options – returns the current set options that are considered user options.

@@rowcount – specifies the number rows returned by the sql statement

@@trancount – specifies the number of currently active or nested transactions

Maintenance stored procedures allow you to centralize your code management. You can easily maintain and/or debug your sql code on the server. You may want to print out or place online your stored procedures so that developers can use them as reference when writing client applications. That way you can make changes to a stored procedure in one location to provide additional features to all the clients that use your stored procedure.

Return codes the RETURN statement terminates the execution of a stored procedure and optionally returns an integer value. You can create a coded return value to communicate additional information from within your stored procedure to a calling application.