Jump Start: The Basics of Working with Variable Values

My November Jump Start columns (see the Related Articles list at the end of this article), broached the topic of using variables in stored procedures, starting with declaring variables using the different T-SQL data types, then jumping into the more advanced topic of using variables in stored procedures to build dynamic SQL statements. I wrote the latter column to give you an idea of just how powerful T-SQL variables can be. Before I get further ahead of myself, though, I want to step back and make sure that we've covered the fundamental concepts for working with T-SQL variables.

The first rule to remember is that you must declare each variable by using a DECLARE statement, and that statement must specify a valid T-SQL or user-defined data type for the variable. Variable names are limited to 128 characters (like you'd really want to use that many) and must begin with an @ character. (For more fundamentals about declaring T-SQL variables, see the SQL Server 2005 Books Online discussion.)

To assign a value to a variable, you can use either a SELECT or a SET statement. Generally, if you're using singleton values, you should write a SET statement. If you need to assign multiple values, you should use SELECT; trying to use a SET statement to assign multiple values causes SQL Server Express to raise an error.

Here's an example that declares three variables and then assigns values to them using SELECT and SET statements:

Declare   @LastName varchar(50),          @FirstName varchar(50),          @FullName varchar(100)Select    @LastName = 'Otey',          @FirstName = 'Michael'SET @FullName = @FirstName + ' ' + @LastName PRINT @FullNameSELECT @FullName

The first SELECT statement above assigns values to both the @LastName and @FirstName variables. The SET statement uses a plus sign (+) to concatenate those values and assign the entire concatenated value to the @FullName variable. Then, the PRINT statement displays the value of @FullName to the client. Alternatively, you can display the contents of a variable using a SELECT statement, which returns a result set.

Related Articles

"Stored Procedure Variables,"

"Using Stored Procedure Variables to Build Dynamic SQL,"