How to Use Variables, IF and CASE in Database Interactions with TransactSQL - Replacing IF with CASE
(Page 5 of 5 )
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
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
/ 23 
