Microsoft® SQL Server® 2012 Bible

438

Part IV: Programming with T-SQL in SQL Server

GO

CREATE PROCEDURE Sales.uspGetCurrencyInformation

@CurrencyCode char(3)

AS

SELECT CurrencyCode, Name

FROM Sales.Currency

WHERE CurrencyCode = @CurrencyCode;

GO

EXECUTE Sales.uspGetCurrencyInformation @CurrencyCode = 'USD';

GO

CurrencyCode Name

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

USD US Dollar

Specifying Parameter Values

You can pass parameter values during stored procedure execution in two ways: named

parameter values and positional parameter values.

Named Parameter Values

When the stored procedure is executed, you can pass parameters as a set of comma-

delimited name-value pairs. The syntax follows:

EXECUTE Sales.uspGetCurrencyInformation @CurrencyCode = 'USD';

When you pass parameters using this method, the order of the provided parameter values

does not matter.

Positional Parameter Values

When you create a stored procedure, the input parameters are defi ned in a specifi c order.

During stored procedure execution, if values are not provided as a collection of name-value

pairs, the position of the parameter values becomes the method by which the stored proce-

dure knows how to assign the variable values within the stored procedure.

EXECUTE Sales.uspGetCurrencyInformation 'USD';

Default Parameter Values

A value must be provided for every defi ned parameter during a stored procedure call.

However, default values can be supplied as part of the stored procedure defi nition. In the

absence of a supplied input parameter value, the default value will be used. If a value is

provided during execution, the default is overridden. A default value is defi ned as part of

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

http://www.it-ebooks.info