Microsoft® SQL Server® 2012 Bible

451

Chapter 17: Developing Stored Procedures

17

FIGURE 17-1

Possible ways for stored procedures to pass data.

Client App

Result set from A

Result set from B

Raiserror

from B

Raiserror

from A

(try...catch) (insert...exec)

Result set

from B

Raiserror

Output Parameters

Return

Proc A

exec

Select

#temp

table

Select

Proc B

DML

exec

Output

Parameters

Return

Nested Stored Procedures and Manageability

Stored procedures that call other stored procedures give you the ability to modularize and reuse your

code, resulting in cleaner, more consistent processing. But be careful; getting carried away with nesting

stored procedures might leave you with a headache if you ever need to troubleshoot a procedure that

is buried multiple levels under the called procedure. Take advantage of SQL Ser ver 2012’s abilit y to pass

data between stored procedures, but balance that with the need for simple, easy to troubleshoot code.

Summary

Stored procedures are a powerful way to implement reuse and consistency in an application.

Data processed by a stored procedure can be used in various ways and is a great option for

everything from returning the results of a basic SELECT statement to performing complex

calculations. Parameterization is the key benefi t of a stored procedure, enabling a single

procedure to return different data based on the value of the passed parameters.

c17.indd 451c17.indd 451 7/30/2012 5:39:36 PM7/30/2012 5:39:36 PM

http://www.it-ebooks.info