Jump Start: Stored Procedure Variables

In "Stored Procedure Parameters" I explained how to pass parameters to T-SQL stored procedures. By their very nature, parameters are T-SQL variables, so now let's talk about how to use variables in stored procedures.

Related: Passing Multivalued Variables to a Stored Procedure

To create variables, you use the T-SQL DECLARE keyword. When you declare a variable, you must also specify its data type. The following code declares several types of variables:

DECLARE @count intDECLARE @name nvarchar(50)DECLARE @sales moneyDECLARE @today datetime

SET @count = 1SET @name = 'Michael Otey'SET @sales = 100.00SET @today = '11/05/2007'

PRINT @countPRINT @namePRINT @salesPRINT @today

As you can see, I've declared four variables, each of which has a different data type. The variable name immediately follows the DECLARE statement and must begin with the @ symbol. You specify the data type after the name--in this case, you can see that I've used the int, nvarchar(50), money, and datatime data types.

Alternatively, you can use a shortcut and declare multiple variables on the same line. For example, the following line does the same thing that the four DECLARE statements in the above listing do:

DECLARE @count int, @name nvarchar(50), @sales money, @today datetime

The SET statements that follow the DECLARE statements assign a value to each data type. The assigned value for a variable must match the range of values accepted by the variable's data type. If the assigned value doesn't fall within the range of allowable values for the data type, SQL Server Express generates an error.

Finally, each PRINT statement in the listing displays the value for one of the variables.

In my next Jump Start column, I'll discuss using variables to construct dynamic SQL statements.