Lesson 5

Transact-SQL statements are made up of actual SQL statements that manipulate or query data from the database. They can also comprise groups of individual SQL statements. These groups of SQL statements can include variables and control-of-flow constructs that enable you to test and execute Transact-SQL statements where specific conditions exist. For example, you can write a single Transact-SQL statement that selects a value from a database, assigns it to a variable, then tests the value of that variable. And you can use control-of-flow constructs to perform an action based on those results.

To use a variable, you must first declare it using the DECLARE keyword, like this:

DECLARE @Variable datatype

Substitute the name of your variable for Variable and a valid datatype for datatype. (We have not covered datatypes yet in this course, but we will later on.) A valid example would be:

DECLARE @LoopCounter Int

Then, to assign a static value to the @LoopCounter variable, you must include the keyword SET. For example, to initialize @LoopCounter to 0, you would use the following syntax:

SET @LoopCounter = 0

Likewise, if you wanted to select a value from the database and place it into a variable, you would use the SELECT keyword. (We'll look at this keyword in detail later in this course.) For example, you could retrieve the maximum value of the LastCount column in the Loop table like this:

SELECT @LoopCounter = MAX(LastCount)
FROM Loop

In the next lesson, we'll take a look at control-of-flow logic, which enables you to test the value of a variable and take an action based on that value.