Microsoft® SQL Server® 2012 Bible

443

Chapter 17: Developing Stored Procedures

17

EXECUTE sp_xml_removedocument @XMLDocHandle;

GO

--Execute the stored procedure

EXECUTE Sales.uspGetCurrencyRatesXML @XMLList ='

<ROOT>

<CurrencyList FromCurrencyCode="USD" ToCurrencyCode="AUD"> </CurrencyList>

<CurrencyList FromCurrencyCode="USD" ToCurrencyCode="EUR"> </CurrencyList>

<CurrencyList FromCurrencyCode="USD" ToCurrencyCode="GBP"> </CurrencyList>

<CurrencyList FromCurrencyCode="USD" ToCurrencyCode="MXN"> </CurrencyList>

</ROOT>', @CurrencyRateDate = '2005-07-14';

The second option to provide a table as an input parameter is to use a table-valued

parameter. On the surface this seems like the obvious choice, but consider a couple of

requirements:

1. To pass a table-valued parameter as an input parameter to a stored procedure, it
   must fi rst be defi ned as a user-defi ned table type.


2. When the user-defi ned table type is specifi ed as an input parameter during stored
   procedure creation, it must be defi ned as READONLY.

The fi rst step to passing a table-valued parameter as an input parameter is to create a user-

defi ned table type.

CREATE TYPE CurrencyCodeListType as TABLE

(

FromCurrencyCode char(3)

,ToCurrencyCode char(3)

);

GO

After you create the user-defi ned table type , it can be used as the data type for the input

parameter in the stored procedure defi nition.

CREATE PROCEDURE Sales.uspGetCurrencyRatesUDT

@CurrencyCodeTable as CurrencyCodeListType READONLY

,@CurrencyRateDate date

AS

SELECT cr.CurrencyRateID, cr.FromCurrencyCode,

cr.ToCurrencyCode, cr.AverageRate,

cr.EndOfDayRate, cr.CurrencyRateDate

FROM Sales.CurrencyRate cr

JOIN @CurrencyCodeTable tvp

ON cr.FromCurrencyCode = tvp.FromCurrencyCode

AND cr.ToCurrencyCode = tvp.ToCurrencyCode

c17.indd 443c17.indd 443 7/30/2012 5:39:35 PM7/30/2012 5:39:35 PM

http://www.it-ebooks.info