Fundamentals of Transact-SQL
In this section, you'll learn some of the essential programming constructs available in T-
SQL. Specifically, you'll see how to use variables, comments, and conditional logic.
You'll also see how to use a number of statements that allow you to perform jumps and
loops. Finally, you'll examine cursors, which allow you to process rows returned from the
database one at a time.
Let's start by looking at variables.
Using Variables
A variable allows you to store a value in the memory of a computer. Each variable has a
type that indicates the kind of value that will be stored in that variable. You can use any
of the types shown earlier in Table 2.3
of Chapter 2, "Introduction to Databases."
You declare a variable using the DECLARE statement, followed by the variable name
and the type. You place an at character (@) before the start of the variable name. The
following syntax illustrates the use of the DECLARE statement:
DECLARE @name type
Where name is the name of your variable, and type is the variable type.
For example, the following statements declare two variables named MyProductName and
MyProductID:
DECLARE @MyProductName nvarchar(40)
DECLARE @MyProductID int
As you can see, MyProductName is of the nvarchar type, and MyProductID is of the int
type.
You can place more than one variable declaration on the same line. For example:
DECLARE @MyProductName nvarchar(40), @MyProductID int
Variables are initially set to null. You set a variable's value using the SET statement. For
example, the following statements set MyProductName to Chai and MyProductID to 7:
SET @MyProductName = 'Chai'
SET @MyProductID = 7
The following SELECT statement then uses these variables in the WHERE clause:

Multi-line comments can of course also span only one line:
/* Another comment */
Using Conditional Logic
Conditional logic allows you to execute different branches of code based on the Boolean
true or false value of a given expression. For example, you might want to check if an
error condition is true and display a message. You use the IF and optional ELSE
keywords to perform conditional logic. The following syntax illustrates the use of
conditional logic:
IF condition
statement1
[ELSE
statement2]
Where condition is a Boolean expression that evaluates to true or false. If condition is
true, then statement1 is executed, otherwise statement2 is executed.

Note You can replace a single statement with multiple statements by placing those
statements within BEGIN and END statements. This rule applies to all T-SQL
programming constructs.
The following syntax shows the replacement of single statements with a block of
statements placed within BEGIN and END:
IF condition
BEGIN
statements1
END
ELSE
BEGIN
statements2
END
Where statements1 and statements2 are multiple statements. You can also use an optional
ELSE statement to execute a different branch of code if the condition is false.

SELECT CASE @State
WHEN 'CA' THEN 'California'
WHEN 'MA' THEN 'Massachusetts'
WHEN 'NY' THEN 'New York'
END

You can store the value retrieved by the SELECT statement in a variable, as shown in the
next example:
DECLARE @State nchar(2)
SET @State = 'MA'
DECLARE @StateName nvarchar(15)
SELECT @StateName =
CASE @State
WHEN 'CA' THEN 'California'
WHEN 'MA' THEN 'Massachusetts'
WHEN 'NY' THEN 'New York'
END
PRINT @StateName
The output from this example is as follows:
Massachusetts
You can also compare a column value in a CASE statement. For example:
SELECT Price =
CASE
WHEN UnitPrice IS NULL THEN 'Unknown'
WHEN UnitPrice < 10 THEN 'Less than $10'
WHEN UnitPrice = 10 THEN '$10'
ELSE 'Greater than $10'
END
FROM Products
You'll notice from this example that you can also supply a catchall ELSE condition in a