Microsoft SQL Server 2012 Bible

Passing Data to Stored Procedures

The characteristic of a stored procedure that makes it a powerful option for code reuse is parameterization. Stored procedures can be defined so that they accept input parameters. The Sales.uspGetCurrencyInformation procedure created in the previous section returned all rows from Sales.Currency. An input parameter can specify which rows should be returned or can completely change the logic of the stored procedure based on how it is coded.

As of SQL Server 2005, the number of parameters that can be specified for a single stored procedure is 2,100.

Input Parameters

Input parameters are a way to provide external information to a stored procedure. You can add input parameters that pass data to the stored procedure by listing the parameters after the procedure name in the CREATE PROCEDURE command. Each parameter must begin with an @ sign and becomes a local variable within the procedure. Like local variables, the parameters must be defined with valid data types. When the stored procedure is called, every parameter value must be included (unless the parameter has a default value).

The following code sample creates a stored procedure that returns a single currency type. The @CurrencyCode parameter can accept a character value with a length of up to three characters. The value assigned to the parameter during stored procedure execution is available within the procedure as the local variable @CurrencyCode in the WHERE clause of the query:

USE AdventureWorks2012 ...