Microsoft® SQL Server® 2012 Bible

440

Part IV: Programming with T-SQL in SQL Server

If a list will be passed into the stored procedure using a string, you must use Dynamic SQL

to construct the T-SQL statement so that it can concatenate the string while maintaining

correct syntax. This approach adds some complexity to the stored procedure and uses the

sp_executesql system stored procedure to execute the query.

USE AdventureWorks2012

GO

CREATE PROCEDURE Sales.uspGetCurrencyRate

@CurrencyRateIDList varchar(50)

AS

DECLARE @SQLString nvarchar(1000)

SET @SQLString = N'

SELECT CurrencyRateID, CurrencyRateDate, FromCurrencyCode,

ToCurrencyCode, AverageRate, EndOfDayRate

FROM Sales.CurrencyRate

WHERE CurrencyRateID in ('+@CurrencyRateIDList+');'

EXECUTE sp_executesql @SQLString;

GO

EXECUTE Sales.uspGetCurrencyRate @CurrencyRateIDList = '1, 4, 6, 7';

The previous example passed in a list of integers and required no special formatting of the

input parameter string. However, when a list of strings is passed into a stored procedure,

the required string formatting becomes more complex.

CREATE PROCEDURE Sales.uspGetCurrencyInformation

@CurrencyCodeList varchar(200) = 'USD'

AS

DECLARE @SQLString nvarchar(1000)

SET @SQLString = N'

SELECT CurrencyCode, Name

FROM Sales.Currency

WHERE CurrencyCode in ('+@CurrencyCodeList+');'

EXECUTE sp_executesql @SQLString;

GO

EXECUTE Sales.uspGetCurrencyInformation

@CurrencyCodeList = '''USD'', ''AUD'', ''CAD'', ''MXN''';

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

http://www.it-ebooks.info