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

Standards

ANSI SQL – Compliance level: Transact-SQL extension.

Permissions

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

See also

Commands print, raiserror, select, while