This is the first of a
series of articles discussing various aspects of T-SQL programming. Whether
you are building a stored procedure or writing a small Query Analyzer script
you will need to know the basics of T-SQL programming. This first article will
discuss defining variables, and using the IF...ELSE logic.
Local Variables
As with any programming language,
T-SQL allows you to define and set variables. A variable holds a single piece
of information, similar to a number or a character string. Variables can be
used for a number of things. Here is a list of a few common variable uses:
-
To pass parameters to stored
procedures, or function
-
To control the processing of a
loop
-
To test for a true or false
condition in an IF statement
-
To programmatically control
conditions in a WHERE statement
In SQL Server a variable is
typical known as a local variable, due the scope of the variable. The scope of
a local variable is only available in the batch, stored procedure or code block
in which it is defined. A local variable is defined using the T-SQL "DECLARE"
statement. The name of the local variable needs to start with a "@" sign as
the first character of its name. A local variable can be declared as any
system or user defined data type. Here is a typical declaration for an integer
variable named @CNT:
DECLARE @CNT INT
More than one variable can
be defined with a single DECLARE statement. To define multiple variables, with
a single DECLARE statement, you separate each variable definition with a comma,
like so:
DECLARE @CNT INT, @X INT, @Y INT, @Z CHAR(10)
Above I have defined 4 local
variables with a single DECLARE statement. A local variable is initially
assigned a NULL value. A value can be assigned to a local variable by using
the SET or SELECT statement. On the SET command you specify the local variable
and the value you wish to assign to the local variable. Here is an example of
where I have defined my @CNT variable and then initialize the variable to 1.
DECLARE @CNT INT
SET @CNT = 1
Here is an example of how to
use the SELECT statement to set the value of a local variable.
DECLARE @ROWCNT int
SELECT @ROWCNT=COUNT(*) FROM pubs.dbo.authors
The above example sets the
variable @ROWCNT to the number of rows in the pubs.dbo.authors table.
One of the uses of a
variable is to programmatically control the records returned from a SELECT
statement. You do this by using a variable in the WHERE clause. Here is an
example that returns all the Customers records in the Northwind database where
the Customers Country column is equal to 'Germany'
Declare @Country varchar(25)
set @Country = 'Germany'
select CompanyName from Northwind.dbo.Customers
where Country = @Country
IF ... ELSE
T-SQL has the "IF" statement
to help with allowing different code to be executed based on the results of a
condition. The "IF" statement allows a T-SQL programmer to selectively
execute a single line or block of code based upon a Boolean condition. There
are two formats for the "IF" statement, both are shown below:
Format one: IF
<condition> <then code to be executed when condition true>
Format two: IF
<condition> <then code to be executed when condition true>
ELSE
< else code to be executed when condition is false>
In both of these formats,
the <condition> is a Boolean expression or series of Boolean expressions
that evaluate to true or false. If the condition evaluates to true, then the
"then code" is executed. For format two, if the condition is false, then the "else
code" is executed. If there is a false condition when using format one, then
the next line following the IF statement is executed, since no else condition
exists. The code to be executed can be a single TSQL statement or a block of
code. If a block of code is used then it will need to be enclosed in a BEGIN and
END statement.
Let's review how "Format one"
works. This first example will show how the IF statement would look to execute
a single statement, if the condition is true. Here I will test whether a variable
is set to a specific value. If the variable is set to a specific value, then I
print out the appropriate message.
Declare @x int
set @x = 29
if @x = 29 print 'The number is 29'
if @x = 30 print 'The number is 30'
The above code prints out
only the phrase "The number is 29", because the first IF statement evaluates to
true. Since the second IF is false the second print statement is not executed.
Now the condition statement
can also contain a SELECT statement. The SELECT statement will need to return
value or set of values that can be tested. If a SELECT statement is used the
statement needs to be enclosed in parentheses.
if (select count(*) from Pubs.dbo.Authors
where au_lname like '[A-D]%') > 0
print 'Found A-D Authors'
Here I printed the message
"Found A-D Authors" if the SELECT statement found any authors in the pubs.dbo.authors
table that had a last name that started with an A, B, C, or D.
So far my two examples only
showed how to execute a single T-SQL statement if the condition is true. T-SQL
allows you to execute a block of code as well. A code block is created by
using a "BEGIN" statement before the first line of code in the code block, and
an "END" statement after that last line of code in the code block. Here is any
example that executes a code block when the IF statement condition evaluates to
true.
if db_name() = 'master'
begin
Print 'We are in the Master Database'
Print ''
Print 'So be careful what you execute'
End
Above a series of "PRINT"
statements will be executed if this IF statement is run in the context of the
master database. If the context is some other database then the print statements
are not executed.
Sometimes you want to not
only execute some code when you have a true condition, but also want to execute
a different set of T-SQL statements when you have a false condition. If this
is your requirement then you will need to use the IF...ELSE construct, that I called
format two above. With this format, if the condition is true then the statement
or block of code following the IF clause is executed, but if the condition
evaluates to false then the statement or block of code following the ELSE
clause will be executed. Let's go through a couple of examples.
For the first example let's
say you need to determine whether to update or add a record to the Customers
table in the Northwind database. The decision is based on whether the customer
exists in the Northwind.dbo.Customers table. Here is the T-SQL code to perform
this existence test for two different CustomerId's.
if exists(select * from Northwind.dbo.Customers
where CustomerId = 'ALFKI')
Print 'Need to update Customer Record ALFKI'
else
Print 'Need to add Customer Record ALFKI'
if exists(select * from Northwind.dbo.Customers
where CustomerId = 'LARSE')
Print 'Need to update Customer Record LARSE'
else
Print 'Need to add Customer Record LARSE'
The first IF...ELSE logic
checks to see it CustomerId 'ALFKI' exists. If it exists it prints the message
"Need to update Customer Record", if it doesn't exist the "Need to add Customer
Record" is displayed. This logic is repeated for CustomerId = 'LARS'. When I
run this code against my Northwind database I get the following output.
Need to update Customer Record ALFKI
Need to add Customer Record LARSE
As you can see from the
results CustomerId 'ALFKI' existed, because the first print statement following
the first IF statement was executed. Where as in the second IF statement CustomerId
'LARSE' was not found because the ELSE portion of the IF...ELSE statement was
executed.
If you have complicated
logic that needs to be performed prior to determining what T-SQL statements to execute
you can either use multiple conditions on a single IF statement, or nest your
IF statements. Here is a script that determines if the scope of the query is
in the 'Northwind' database and if the "Customers" table exists. I have
written this query two different ways, one with multiple conditions on a single
IF statement, and the other by having nested IF statements.
-- Single IF Statement with multiple conditions
use Northwind
if db_name() = 'Northwind' and
(select count(*) from sysobjects
where name = 'Customers') = 1
print 'Table Customers Exist'
else
print 'Not in the Northwind database' +
' or Table Customer does not exist'
-- Nested IF Statements
use Northwind
if db_name() = 'Northwind'
if (select count(*) from sysobjects
where name = 'Customers') = 1
print 'Table Customers Exist'
else
print 'Table Customer does not exist'
else
print 'Not in the Northwind Database'
As you can see I tested to
see if the query was being run from the Northwind database and whether the
"Customers" table can be found in sysobjects. If this was true, I printed the
message "Table Customers Exists". In the first example I had multiple
conditions in a single IF statement. Since I was not able to determine which
parts of the conditions in the IF statement where false the ELSE portion
printed the message "Not in Northwind database or Table Customer does not exist".
Now in the second example where I had a nested IF statement I was able to
determine whether I was in the wrong database or the object "Customers" did not
exist. This allowed me to have two separate print statements to reflect
exactly what condition was getting a false value.
Conclusion
I hope that this article has helped you understand how to declare and use local
variables, as well as IF...ELSE logic. Local variables are useful to hold the
pieces of information related to your programming process. Where as the IF
statement helps control the flow of your program so different sections of code
can be executed depending on a particular set of conditions. As you can see
nesting IF statements and/or having multiple conditions on an IF statement allows
you to further refine your logic flow to meet your programming requirements. My
next article in this T-SQL programming series will discuss how to build a programming
loop.
»
See All Articles by Columnist Gregory A. Larsen
» 
Comment and Contribute
