Microsoft® SQL Server® 2012 Bible

441

Chapter 17: Developing Stored Procedures

17

The string value must be assigned to the input parameter as a single value, but when it is

concatenated into the Dynamic SQL, it must be recognized as a list of strings. This means

that each individual string value must be wrapped in two single tick marks (‘‘) rather than

one, and the entire list must be qualifi ed with a single tick mark (‘). This can create a

headache for a developer who tries to call the stored procedure. Luckily, there is more than

one approach available to pass a list into a stored procedure as an input parameter. Another

option is to use an XML data type.

XML can be intimidating for someone who hasn’t used it before, but after a comfort level

is achieved with using this fl exible data type, the value of XML becomes apparent. In the

next example, a list is passed in XML format, an XML document is prepared using the sys-

tem stored procedure sp_xml_preparedocument, and then the contents of the XML docu-

ment are inserted into an internal table variable using the OPENXML function. It sounds

confusing, but it is actually an effi cient way to pass a list to a stored procedure.

For more information on working with XML, see Chapter 14, “Using XML Data.”

CREATE PROCEDURE Sales.uspGetCurrencyInformationXML

@XMLList varchar(1000)

AS

DECLARE @XMLDocHandle int

DECLARE @CurrencyCodeTable table

(

CurrencyCode char(3)

)

EXECUTE sp_xml_preparedocument @XMLDocHandle OUTPUT, @XMLList;

INSERT INTO @CurrencyCodeTable(CurrencyCode)

SELECT CurrencyCode

FROM OPENXML (@XMLDocHandle, '/ROOT/Currency',1)

WITH (

CurrencyCode char(3)

);

SELECT c.CurrencyCode, c.Name, c.ModifiedDate

FROM Sales.Currency        c

JOIN @CurrencyCodeTable tvp

ON c.CurrencyCode = tvp.CurrencyCode;

EXECUTE sp_xml_removedocument @XMLDocHandle

GO

--Execute the stored procedure, passing in XML

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

http://www.it-ebooks.info