Microsoft® SQL Server® 2012 Bible

439

Chapter 17: Developing Stored Procedures

17

the input parameter defi nition; simply place "= default value" after the input param-

eter information.

USE AdventureWorks2012

GO

ALTER PROCEDURE Sales.uspGetCurrencyInformation

@CurrencyCode char(3) = 'USD'

AS

SELECT CurrencyCode, Name

FROM Sales.Currency

WHERE CurrencyCode = @CurrencyCode;

GO

Now that a default parameter value is defi ned, there are two ways to tell the stored pro-

cedure to use the default value. The fi rst is by not passing any information to the stored

procedure:

EXECUTE Sales.uspGetCurrencyInformation;

The second method is to use the DEFAULT keyword during the stored procedure call. This

can come in handy if multiple input parameters are required, but only some of them should

use the default value:

EXECUTE Sales.uspGetCurrencyInformation @CurrencyCode = DEFAULT;

EXECUTE Sales.uspGetCurrencyInformation DEFAULT;

All the preceding stored procedures produce the same result set because the default value

for @CurrencyCode is USD.

CurrencyCode Name

------------ --------------------------------------------------

USD US Dollar

Providing Lists and Tables as Input Parameters to Stored

Procedures

One of the most common challenges a developer can encounter when implementing stored

procedures is how to pass multiple values into a stored procedure with a single input param-

eter. Multiple options are available, and the most common approaches are covered here.

Providing a List as an Input Parameter

Two methods to pass a list as an input parameter are shown here. The fi rst method is to

construct a string that is suitable for an IN clause, and the second is to pass a list that is

then turned into a table that can be included in a table join.

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

http://www.it-ebooks.info