445
Chapter 17: Developing Stored Procedures
17
Best Practice
With every operation that affects data, SQL Server, by default, also sends a message stating the number
of rows affected or returned. In most cases, this information is not used by the calling application and
is just extra information (and an extra server round trip). You can eliminate this message and improve
performance by using SET NOCOUNT ON at the beginning of a stored procedure defi nition.
CREATE PROCEDURE schema.StoredProcedureNameASSET NOCOUNT ON;
...Output Parameters
Output parameters enable a stored procedure to return data to the calling application.
The keyword OUTPUT is required both when the procedure is created and when it is called.
Within the stored procedure, the output parameter appears as a local variable, just like an
input parameter. Although output parameters are typically used solely for output, they are
actually two-way parameters. When the stored procedure concludes, its current value is
passed to the calling application.Best Practice
Output parameters are useful for returning single units of data when a whole record set is not required.
For returning single values, an output parameter can perform better than returning the data as a result set.In the calling application, a local variable must have been created to receive the value of
the output parameter. This can be illustrated within SQL Server Management Studio (SSMS).- CREATE the stored procedure Sales.uspGetCurrencyName with the OUTPUT
parameter @CurrencyName. - DECLARE the local variable @CurrencyNameOutput to receive the output
parameter. - EXECUTE the stored procedure, assigning the output parameter the value of @
CurrencyNameOutput, which is NULL until it has been initialized. - During stored procedure execution, the output parameter is assigned a value via
the SELECT statement based on the value of the input parameter @CurrencyCode.
c17.indd 445c17.indd 445 7/30/2012 5:39:36 PM7/30/2012 5:39:36 PM
http://www.it-ebooks.info