How to Use Variables, IF and CASE in Database Interactions with TransactSQL - Using WHERE conditions in the IF statement

(Page 4 of 5 )

The "IF" conditions can include almost all types of conditions being used as part of a "WHERE" clause. These include SQL operators such as IN, IS NULL, LIKE, BETWEEN..AND, and so forth.

Let us work with a small example which includes a BETWEEN..AND condition as part of an ELSE-IF ladder. The following is the script:

use northwind
go

declare @EmpSales numeric(12,2)
declare @EmpID int

set @EmpID = 3
set @EmpSales = (select sum(unitprice * quantity)
                 from [order details]
                 where orderid in (select orderid from orders
                                   where employeeid=@EmpID))
if @EmpSales < 100000
   print 'Bad sales: ' + convert(varchar, @EmpSales)
else if @EmpSales between 100000 and 200000
   print 'Normal sales: ' + convert(varchar, @EmpSales)
else
   print 'Good sales: ' + convert(varchar, @EmpSales)
go

From the above code, you can observe that I am working with an ELSE-IF ladder (it can include any number of steps) together with a BETWEEN..AND condition. Any IF structure can be nested any number of times.

The IF condition with SELECT statements

An IF condition can even include SELECT statements. This is a bit different from the traditional approach of IF conditions. Let us examine the following script:

use northwind
go

declare @EmpSales numeric(12,2)
declare @EmpID int

set @EmpID = 2
set @EmpSales = (select sum(unitprice * quantity)
                 from [order details]
                 where orderid in (select orderid from orders
                                   where employeeid=@EmpID))
if @EmpSales < (select avg(unitprice * quantity)
                from [order details])
   print 'Bad sales: ' + convert(varchar, @EmpSales)
else
   print 'Good sales: ' + convert(varchar, @EmpSales)
go

According to the above script, the value of "@EmpSales" is checked against the average price of all orders. We can even include two SELECT statements in the same condition as follows:

use northwind
go

declare @EmpID int

set @EmpID = 2
if (select sum(unitprice * quantity)
    from [order details]
    where orderid in (select orderid from orders
                      where employeeid=@EmpID))
 < (select avg(unitprice * quantity)
    from [order details])
   print 'Bad sales'
else
   print 'Good sales'
go

Testing for row existence in a table is a frequently used feature in T-SQL programming. The EXISTS operator available as part of SQL can also be used here to test for the same. The following is an example:

use northwind
go

declare @EmpID int
declare @Ename varchar(20)
set @EmpID = 2
if exists (select * from Employees
           where EmployeeID = @EmpID)
   print 'Employee found'
else
   print 'Employee not found'
go

Next: Replacing IF with CASE >>
 

More MS SQL Server Articles
More By Jagadish Chaterjee

Comments On: How to Use Variables, IF and CASE in Database Interactions with TransactSQL

· Hello guys,This is second in series focusing on the programming with...    · I was able to retrieve value of a column which is not updated!Declare @Location...    · Wow that is great. I'm going to try a couple of tricks and see what happens. ...   >>> Post your comment now!