The role of stored procedures in a business application is invaluable to the entire solution process. Any business transaction application should make use of stored procedures to execute read and DML statements from SQL Server. Even if the middle business logic tier contains the majority of the business logic, the business layer should use stored procedures to communicate directly with SQL Server. Stored procedures offer a palpable performance advantage even if the procedures are supplying read only data to an application through a business logic tier.
Stored procedures are precompiled in SQL Servers procedure cache. When an application or business tier server product (such as MTS) submits data to SQL Server for processing, they typical steps involved in ad hoc query processing need not occur. Steps such as syntax check, object verification and query plan inception, evaluation and verification need not occur. Because the stored procedure has been created and compiled, these aforementioned steps have already been accomplished and stored in SQL Server procedure cache. The execution plan is read from cache and then executed.
courtesy Microsoft SQL Server Books Online
Reuse of a precompiled
Stored Procedure with
multiple input parameters
Communicating with Stored Procedures
Data to the Client
Applications utilizing Active X Data Objects (ADO) or OLE DB will interact with stored procedures output on a recordset level. Any read data required by an application for presentation to the user should be retrieved through a stored procedure. The application using ADO or OLE DB will interpret the results of the select statement in a stored procedure as a recordset. Developers can then use the recordset to populate forms, reports or other client interfaces.
Data from the Client
Applications can directly make reference to a stored procedure, independent of a business logic tier. Using ADO, an application can collect data from the user interface (ASP forms, VB forms), call the stored procedure and pass form data to a stored procedure as an ADO parameter. The SQL Server stored procedure accepts the input parameters and executes the SQL task.
Business logic tier products such as MTS facilitate the creation of logical objects which the client application references. When the client makes reference to a desired transactional function (i.e., Add an new customer), the client application calls an MTS object that then initiates the communication with SQL Server on the clients behalf. The MTS object calls the stored procedure that then executes on the SQL Server. Return values from procedure execution can be passed directly to the client.
Stored Procedure Design
Proper stored procedure programming is similar to the concepts of programming T-SQL batches or T-SQL scripts. The contents of a stored procedure consist of any T-SQL that can be written using a batch or script. All SQL programming techniques should be implemented, including maintaining ANSI compatibility.
Stored procedures cannot contain many Data Definition Language (DDL) statements. Views, defaults, new stored procedures or triggers are among some of the objects that stored procedures cannot create when they are called. However, other database objects can be created, such as tables or temporary tables. Stored procedures can also reference other stored procedures.
Error handling should be an integral component of a stored procedure. Using error handling techniques such as the RAISERROR statement, T-SQL programmers can included custom defined error messages that can be returned to and interpreted by the client. The client can then present the user with the exact SQL Server error message or further customize it based on programming in the client presentation layer. Integration of custom error messages with the sysmessages table using the sp_addmessage system stored procedure allows for ease of message management and consistency.
Input parameters in a stored procedure should be set to a null value. Using this method, the stored procedure allows for a condition where the client application does not pass all expected input parameters. The stored procedure can still execute by evaluating the other parameters.
Naming conventions for stored procedures should follow a logical and consistent formula. Similar to a table object naming convention, stored procedure names should not contain control characters, spaces or symbols. Stored procedure names should also avoid utilizing the sp_ prefix as this prefix is used by SQL Server system stored procedures. A more appropriate methodology might be to name a procedure P_ + object name.
Stored procedures should encompass transactional consistency. In other words, if a business transaction, initiated by the client, passed via a business tier server object (MTS) to SQL Server, requires several updates, deletes and inserts to multiple tables to complete, all of the required SQL DML statements should execute. Otherwise, if a catastrophic event occurs, all SQL statements defining the transaction should be rolled back.
Using this method, transactions requiring multiple DML statements will complete in their entirety, or not at all. This will increase data integrity in situations where a multipart transaction begins, completes half of the required SQL DML statements and is interrupted (due to power outages or catastrophic hardware or software failure). When SQL Server is recovered or the server reboots and the SQL Server service restarts, the transaction log is scanned for uncommitted transactions. If the transaction is not committed by the stored procedure, all of the SQL DML initiated in the transaction will be rolled back. The transaction must then be executed again and complete in its entirety.