Databases Reference
In-Depth Information
To create a
local variable,
which exists only in the context of the batch in
which it is defined, you use a commercial
at
symbol (
@
) as the first character
in the variable name. The syntax for defining, or declaring, a local variable is:
DECLARE @
name data_type
You can specify a static (constant) value or an expression, as long as the
value is compatible with the data type. Whenever possible, SQL Server will
implicitly convert the value if it is not of the same type, but is of a compatible
type. Use either SET or SELECT to assign a value to a variable. Here's an exam-
ple of declaring a variable and assigning a value:
DECLARE @num int
SET @num = 5
Here's the same example, using SELECT:
DECLARE @num int
SELECT @num = 5
For comparison, here's an example where an expression is used to supply
the value:
DECLARE @right_now datetime
SET @right_now = GETDATE()
Now, let's take a look at a batch that declares a variable, sets its value, then
uses the variable in a DML statement:
DECLARE @avgpct REAL
SELECT @avgpct = (SELECT AVG(COMMPERCT) FROM SALESPERSON)
UPDATE SALESPERSON SET COMMPERCT = @avgpct
This is functionally the same as an example you saw earlier. You declare a REAL
type variable named @avgpct and set it equal to the average of all commission
percentages. Then, you use the variable in an UPDATE statement to set COMM-
PERCT to that value for all salespersons.
Using Control Statements
We're going to limit our discussion to some simple control statements. These are
some of the statements that you are most likely to encounter, plus a way of
adding statements that don't execute so you can document your script. For more
information about control language and its use with SQL Server, refer to the
Books Online that install with SQL Server and the Microsoft MSDN Web site for
“Control-of-flow language” and follow the links provided.
We'll start with a pair of keywords: BEGIN and END. You use BEGIN and
END to group a set of statements as a block. They are typically used together
with other control statements to set the context for execution. You'll see how
this is done in a moment.
