declare

Description

Declares the name and type of local variables for a batch or procedure.

Syntax

Variable declaration:

declare @variable_name datatype 
	[, @variable_name datatype]...

Variable assignment:

select @variable = {expression | select_statement} 
	[, @variable = {expression | select_statement} ...]
	[from table_list] 
	[where search_conditions] 
	[group by group_by_list] 
	[having search_conditions] 
	[order by order_by_list] 
	[compute function_list [by by_list]]

Parameters

@variable_name: must begin with @ and must conform to the rules for identifiers.

datatype: can be either a system datatype or a user-defined datatype.

Examples

Example 1

Declares two variables and prints strings according to the values in the variables:

declare @one varchar(18), @two varchar(18) 
select @one = "this is one", @two = "this is two" 
if @one = "this is one" 
	print "you got one" 
if @two = "this is two" 
	print "you got two" 
else print "nope"

you got one
you got two

Example 2

Prints “Ouch!” if the maximum book price in the titles table is more than $20.00:

declare @veryhigh money 
select @veryhigh = max(price) 
	from titles 
if @veryhigh > $20 
	print "Ouch!"

Usage

Assign values to local variables with a select statement.
The maximum number of parameters in a procedure is 2048. The number of local or global variables is limited only by available memory. The @ sign denotes a variable name.
Local variables are often used as counters for while loops or if...else blocks. In stored procedures, they are declared for automatic, noninteractive use by the procedure when it executes. Local variables must be used in the batch or procedure in which they are declared.
The select statement that assigns a value to the local variable usually returns a single value. If there is more than one value to return, the variable is assigned the last one. The select statement that assigns values to variables cannot be used to retrieve data in the same statement.
The print and raiserror commands can take local variables as arguments.
Users cannot create global variables and cannot update the value of global variables directly in a select statement.

Standards

SQL92 – Compliance level: Transact-SQL extension.

Permissions

declare permission defaults to all users. No permission is required to use it.