449
Chapter 17: Developing Stored Procedures
17
FROM Sales.Currency
WHERE CurrencyCode = @CurrencyCode;--Detail Information
SELECT FromCurrencyCode, ToCurrencyCode,
AverageRate, EndOfDayRate, CurrencyRateDate
FROM Sales.CurrencyRate
WHERE FromCurrencyCode = @CurrencyCode
AND CurrencyRateDate = @CurrencyRateDate;GO--Execute the stored procedure
EXECUTE Sales.uspGetCurrencyInfoAndDetail
@CurrencyCode = 'USD',
@CurrencyRateDate = '2007-07-14'
WITH RESULT SETS
(
( [Currency Code] char(3)
,[Currency Name] varchar(50)
)
, -- Separate each defined result set with a comma
( [From Currency] char(3)
,[To Currency] char(3)
,[Average Rate] numeric(7,4)
,[End of Day Rate] numeric(7,4)
,[Currency As-Of Date] date
)
);WITH RESULT SETS enables the EXECUTE statement to defi ne column names and
data types. All columns returned by the stored procedure must be defi ned in the
WITH RESULT SETS defi nition, and assigned data types must be compatible with the
data types of the returned columns.INSERT...EXECUTE
One variation on the concept of result sets is to pass data directly from a stored proce-
dure to a table. This is known as an INSERT...EXECUTE. The following example inserts the
results of the stored procedure directly into a new table in AdventureWorks2012.USE AdventureWorks2012
GO--Create New Table
CREATE TABLE Sales.NewCurrency
(
CurrencyCode char(3)
,Name varchar(50)c17.indd 449c17.indd 449 7/30/2012 5:39:36 PM7/30/2012 5:39:36 PM
http://www.it-ebooks.info