T-SQL IF ELSE – A Conditional Statement

java-user-inputWhether you are building a stored procedure or writing a small query script you will need to know the basics of T-SQL programming. Defining variables used in a program and understanding the IF…ELSE logic is the focus of our discussion today.

What is T-SQL?

Transact-SQL or T-SQL is the proprietary procedural language that Microsoft SQL Server uses. It is a variant of the ANSI-standard Structured Query Language, SQL. A procedural language is designed to extend the abilities of SQL’s while maintaining integration with SQL.

If you want to learn the fundamentals of Microsoft SQL, then the courses Microsoft SQL Database Fundamentals  or Microsoft SQL Server 101 will give you the required introductory knowledge.

Defining and Setting Variables

Like any programming languages, 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 are some of the common application of a variable:

  • 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 an SQL Server a variable is typically 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. A local variable should be declared as any system or user defined data type. Here is an example of the syntax to be used to declare an integer variable named CNT.

Syntax:

DECLARE @CNT INT

You can define more than one variable with a single DECLARE statement. To define multiple variables, with a single DECLARE statement, you use comma after each variable definition. In the example below four local variables have been defined with one single DECLARE statement.

Syntax:

DECLARE @CNT INT, @X INT, @Y INT, @Z CHAR(10)

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. Here is an example where the CNT variable has been defined as an integer and then initialized to the value 1.

Syntax:

DECLARE @CNT INT

SET @CNT = 1

You can also use the SELECT statement to set the value of a local variable. The example sets the variable @COLCNT to the number of columns in the name.db.authors table.

Syntax:

DECLARE @COLCNT int

SELECT @COLCNT=COUNT(*) FROM name.db.authors

IF…ELSE T-SQL Statement – What is it About?

The IF…ELSE T-SQL statement imposes conditions on the execution of a Transact-SQL (T-SQL) statement. The T-SQL statement that follows an IF keyword is executed if its condition is satisfied. In that case the Boolean expression returns a TRUE value.

When the IF condition is not satisfied and the Boolean expression returns FALSE, the optional ELSE keyword introduces another Transact-SQL statement and condition which is executed. This IF…ELSE statement is very similar to that of C Programming.

Examples and Syntax of T-SQl IF…ELSE

Example 1: Using a simple Boolean expression

IF…Else can be used to execute a simple Boolean expression, which returns a true value when 1=1, and therefore prints the first statement. If the result is false then the second statement is printed. A Boolean expression returns a TRUE or FALSE value.

Syntax:

IF 2 = 2 PRINT ‘Boolean_expression is true.’

ELSE PRINT ‘Boolean_expression is false.’ ;

Example 2: Using a query as part of a Boolean expression

If the Boolean expression contains a SELECT statement, then the SELECT statement must be enclosed in parentheses.

The following example executes a query as part of a Boolean expression. If there are 20 cars in the Product table that meet the WHERE clause, then the first print statement will be executed.

Syntax:

 USE CarModelWorks2010;

GO

IF

(SELECT COUNT(*) FROM Production.Product WHERE Name LIKE ‘Jeep-1000%’ ) > 15

PRINT ‘There are more than 15 Jeep -1000 Cars.’

ELSE PRINT ‘There are 15 or less Jeep -1000 Cars.’ ;

GO

Example 3: Grouping Code into Blocks

Sometimes you need to treat a group of statements as a group of statements as though they were all one block of statements. If you execute one then the other statements in the block will be executed. A Transact-SQL statement or statement grouping is defined by a statement block. Unless a statement block is used, the IF or ELSE condition may be imposed on only one Transact-SQL statement or the very next statement after the IF or ELSE. To define a statement block or to include multiple statements as part of your control-of-flow block, use the keywords BEGIN and END.

Syntax:

IF Boolean_expression { sql_statement | statement_block }

[ ELSE { sql_statement | statement_block } ]

Several IF statements can be either nested after another IF or can follow an ELSE statement. The limit to the number of nested levels depends on available memory.

The example here is that of a statement block with a nested IF…ELSE statement. The statements in this example are used to identify if a number is large, small, or medium.

Syntax:

DECLARE @Number int;

SET @Number = 60;

IF @Number > 150

PRINT ‘The number is large.’;

ELSE

BEGIN

IF @Number < 20

PRINT ‘The number is small.’;

ELSE

PRINT ‘The number is medium.’;

END ;

GO

Improper Use of NULL with IF

Sometimes you need to debug your program for improper usage of NULL.

Syntax:

If @myvar = NULL

This will never be true on most systems and the program will bypass all the NULL value. The correct syntax should read as:

Syntax:

If @myvar IS NULL

You will see the above behavior if the ANSI_NULLS option is set to ON, which is the default option.

Conclusion

Hope this discussion gave you a basic understanding about how to declare and use local variables, and how to apply an IF…ELSE logic. An IF statement helps you to control the flow of your program on the basis of certain conditions. Different sections of code can be executed depending on a particular set of conditions. You can execute complex logic with multiple conditions by using nested IF…ELSE statements.

If you want to take your learning process little further and want to prepare for the Microsoft certification exams then Microsoft SQL Server 2012 Certification Training Exam 70-462 and Microsoft SQL Server 2012 Certification Training Exam 70-463 will help you study for the exams and understand how to implement and administer Microsoft SQL Server.