Microsoft® SQL Server® 2012 Bible

448

Part IV: Programming with T-SQL in SQL Server

@CurrencyName = @CurrencyNameOutput OUTPUT;

PRINT @CurrencyNameOutput;

PRINT @ReturnCode;

GO

Result Sets

The easiest way to see the output of a stored procedure is with a result set. When a result

set is created within a stored procedure, it is returned to the calling application. This

stored procedure returns a single result set, containing all the rows in Sales.Currency.

USE AdventureWorks2012

GO

CREATE PROCEDURE Sales.uspGetCurrencyTable

AS

SELECT CurrencyCode, Name

FROM Sales.Currency;

GO

EXECUTE Sales.uspGetCurrencyTable;

WITH RESULT SETS

With the release of SQL Server 2012, there is a new option called WITH RESULT SETS. This

feature allows the EXECUTE statement to defi ne the structure of one or more result sets

that are returned by a stored procedure. Assume that for a single currency code, the calling

application expects two result sets: currency code information and currency rate informa-

tion. This can be defi ned cleanly with this approach.

The stored procedure makes no reference to the result set defi nition. Because the EXECUTE

statement defi nes the result sets, this means that a calling application can defi ne results in

different ways based on the context of the procedure call.

USE AdventureWorks2012

GO

CREATE PROCEDURE Sales.uspGetCurrencyInfoAndDetail

@CurrencyCode char(3)

,@CurrencyRateDate date

AS

--Header Information

SELECT CurrencyCode, Name

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

http://www.it-ebooks.info