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

The examples in this series were tested on Microsoft SQL Server 2005 on Windows Server 2003 Enterprise Edition (and Windows XP Professional Edition).  I didn't really test the examples with any other/previous editions (as I am concentrating on Microsoft SQL Server 2005). If you have any problems with executing the code, please post in the discussion area.

Using variables with the UPDATE command

In my previous article, I introduced variables and their usage in T-SQL scripts. Now, I shall extend the concept of variables to the UPDATE statement. Let us consider the following script:

use northwind
go
set nocount on
go
declare @LatestValue money

update [Order Details]
   set UnitPrice = UnitPrice + 2.5
   where OrderID = 10248
   and ProductID = 11
set @LatestValue = (select UnitPrice from [order details]
                    where OrderID = 10248
                    and ProductID = 11)
print 'Updated Value: ' + convert(varchar,@LatestValue)
go

Whenever a DML statement is executed, the output includes the number of rows affected by the DML statement. To suppress that message we can use "set nocount on." According to the above script, I declared a variable, @LatestValue, which should contain the updated unit price for the respective order and product.

In the above script, the UPDATE statement modifies (or increments) the unit price. As the UPDATE would not return any value, I used a separate SELECT statement to retrieve the updated value and finally assigned the same to the @LatestValue variable.

Instead of using two separate statements (UPDATE and SELECT), we can achieve the same using a single UPDATE statement as follows:

use northwind
go
set nocount on
go
declare @LatestValue money

update [Order Details]
   set @LatestValue = UnitPrice = UnitPrice + 2.5
   where OrderID = 10248
   and ProductID = 11

print 'Updated Value: ' + convert(varchar,@LatestValue)
go

In the above UPDATE statement, the modified unit price (unit price + 2.5) gets assigned to both a row (for the column UnitPrice) and a variable (@LatestValue) at the same time!

Generating serial numbers (or sequences) and cumulative sums using the UPDATE statement

Let us consider that I have a table with several rows. I would like to add a new column of the numeric type and fill it with serial numbers. You can achieve this by  using an UPDATE statement together with an auto-updatable variable.

Before proceeding with the script, we need to create a table with existing rows and add a new column to fill in the serial numbers. Execute the following command, which creates a new table named "SampleCustomers" (with exactly the same structure and rows as is available in the Customers table) along with a new column SlNo (filled with default zeroes):

select 0 SlNo, * into SampleCustomers from customers

At this point, if you open the SampleCustomers table, it would contain a new column named SlNo (filled with zeroes) together with all the columns and rows from existing Customers table.

Now, I would like to update all the rows in that table with serial numbers using an UPDATE statement. The following is the script that will achieve the same: 

use northwind
go
set nocount on
go
declare @SlNo int
set @SlNo = 0
update SampleCustomers
   set @SlNo = SlNo = @SlNo + 1

print 'Updated Successfully'
go

You can also achieve this using the IDENTITY column. But I would like to demonstrate the efficiency of variables together with the UPDATE statement.

We can create not only sequences, but also running or cumulative sums and update them as part of the table itself. Let us now create another sample table which contains an Order wise price list and an extra column to store running sums (defaults with zeroes) as follows:

select OrderID, Amt, 0 RunningSum into SampleOrders
from
(
select orderid, sum(unitprice * quantity) amt
from [order details]
group by orderid
)as a

Now, we can update the RunningSum with values using the following script:

use northwind
go
set nocount on
go
declare @sum int
set @sum = 0
update SampleOrders
   set @sum = RunningSum = @sum + Amt

print 'Updated Successfully'
go 

Introducing the IF statement in T-SQL

No programming language exists without the IF statement. It is a conditional control statement which executes a set of statements based on the condition provided by the user. Let us consider 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 > 200000
   print 'Good sales: ' + convert(varchar, @EmpSales)
else
   print 'Normal sales: ' + convert(varchar, @EmpSales)
go

In the above script, the "IF" condition simply checks whether employee sales have crossed 200000 or not. If the sales value is more than 200K, it prints out the sales value with message "Good Sales." If the sales value is less than (or equal to) 200K, it prints out the sales value with the message "Normal Sales."

If you would like to include more than one statement as part of the true/false parts, you need to embed them within a "begin..end" structure as follows:

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 > 200000
   begin
   print 'Good sales: ' + convert(varchar, @EmpSales)
   print 'keep it up'
   end
else
   begin
   print 'Normal sales: ' + convert(varchar, @EmpSales)
   print 'Try hard to achieve Good sales'
   end
go

Using WHERE conditions in the IF statement

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

Replacing IF with CASE

CASE structure is very similar to IF structure (but may not replace it sometimes). The CASE structure can include any number of conditions using WHEN..THEN. The following is a sample script:

use northwind
go

declare @EmpSales numeric(12,2)
declare @EmpID int
declare @Status varchar(20)
set @EmpID = 2
set @EmpSales = (select sum(unitprice * quantity)
                 from [order details]
                 where orderid in (select orderid from orders
                                   where employeeid=@EmpID))

set @Status = case
             when @EmpSales < 100000 then 'Bad Sales'
             when @EmpSales between 100000 and 200000 then 'Normal Sales'
             else 'Good Sales'
           end

print @Status + ': ' + convert(varchar, @EmpSales)
go

In the above script, I used case as part of an expression. This facility is not available with the IF structure. The IF structure must be completely independent. According to the above script, if the WHEN condition is met the value ("Bad Sales" etc.) gets directly assigned to the variable @Status and finally gets displayed using the PRINT statement.

In the next article, I shall concentrate a bit more on conditions, loops, etc. So, please check back frequently or sign up for a newsletter! Any bugs, errors, improvements etc., are highly appreciated at http://jagchat.spaces.live.com