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.
