Professional SQL Server™ 2005 Programming

11.4. Parameterization

A stored procedure gives you some (or in the case of .NET, a lot of) procedural capability, and also gives you a performance boost in the form of precompiled code that has already been run through the optimizer, but it wouldn't be much help in most circumstances if it couldn't accept some data to tell it what to do. Likewise, you often want to get information out of the sproc — not just one or more recordsets of table data but also information that is more direct. An example here might be if you update several records in a table and you want to know just how many you updated. Often, this isn't easily handed back in recordset form, so you make use of an output parameter.

From outside the sproc, parameters can be passed in either by position or by reference. From the inside, it doesn't matter which way they come in. They are declared the same either way.

11.4.1. Declaring Parameters

Declaring a parameter requires two to four of these pieces of information:

• The name

• The data type

• The default value

• The direction

The syntax follows:

@parameter_name [AS] data type [= default|NULL] [VARYING] [OUTPUT|OUT]

The rules for naming parameters are basically the same as those for a variable. The data type also matches the rules of a variable.

One special thing in declaring the data type is to remember that, when declaring a parameter of type CURSOR, you must also use the VARYING and OUTPUT options. The use of this type of parameter is pretty unusual, but keep it in mind. ...