Using @@ROWCOUNT and TABLE Variables for Database Interactions with Transact-SQL - CASE as part of IF condition in T-SQL
(Page 2 of 4 )
In this section, I would like to combine the CASE structure with IF as part of the condition. Let us go through 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 (case
when @EmpSales < 100000 then 'Bad Sales'
when @EmpSales between 100000 and 200000 then 'Normal Sales'
else 'Good Sales'
end) = 'Good Sales'
print 'Good keep it up'
else
print 'You may have to improve your sales. Currently it is only: ' + convert(varchar, @EmpSales)
go
The CASE structure in the above example returns one of thee values (bad sales, normal sales or good sales) based on the employee sales retrieved and stored in @EmpSales. The IF structure simply tests whether or not the value returned by the CASE structure is "Good Sales." If it is, it displays a positive message; otherwise it displays a different message.
The above script may not be practical in most scenarios. But my intention is simply to introduce you to the point that a CASE structure can also be a part of an IF condition.
Using @@ROWCOUNT to retrieve the number of rows affected
When a DML statement (either INSERT, UPDATE, and DELETE) is issued to a database, the operation either will or will not be successful. For example, if the UPDATE statement doesn't update any information in the database, sometimes, it may not reflect back, giving an "Unsuccessful" result.
This scenario will mostly occur when you provide a condition which would never be satisfied. Let us consider the following example:
use northwind
set nocount on
go
declare @EmpID int
set @EmpID = 9999
update Employees set FirstName = 'Jag'
where EmployeeID = @EmpID
print 'Successfully updated'
go
In the above script, I am issuing an UPDATE statement with a condition which would never be satisfied (an employeeid with 9999). But when you execute the above script, you will simply get the same message: "Successfully updated."
The problem is that we need to test whether the latest DML command has updated the database or not. If it did update anything, then we only need to display that message. This is where @@ROWCOUNT comes in. It simply returns the number of rows affected by the latest DML command. Let us modify the above script as follows:
use northwind
set nocount on
go
declare @EmpID int
set @EmpID = 9999
update Employees set FirstName = 'Jag'
where EmployeeID = @EmpID
if @@ROWCOUNT = 0
print 'No operation took place'
else
print 'Successfully updated'
go
Now, you can observe from the above script that I am using @@ROWCOUNT to retrieve the number of rows affected by the UPDATE statement. If no rows are affected, it returns 0 and thus we can display a better message to the user.
Next: A brief introduction to TABLE variables in T-SQL programming >>
More MS SQL Server Articles
More By Jagadish Chaterjee
