Using @@ROWCOUNT and TABLE Variables for Database Interactions with Transact-SQL (Page 1 of 4 )
This is the third article in a series focusing on programming with Transact-SQL. In this article, I shall go a bit in-depth into CASE structure and introduce two new topics, @@ROWCOUNT and TABLE variables in Transact-SQL.
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 because I am concentrating on Microsoft SQL Server 2005. Please understand that all the samples in this series are meant only for learning purposes. They may not be practical in some scenarios. If you have any problems with executing the code, please post in the discussion area.
CASE structure with SELECT statements as conditions
In my previous articles, I covered defining, initializing and using variables in scripts along with IF and CASE structures. In this section, I would like to introduce SELECT statements as part of CASE structure. Let us go through the following 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 < (select avg(unitprice * quantity)
from [order details]) then 'Low sales'
else 'Good Sales'
end
print @Status + ': ' + convert(varchar, @EmpSales)
go
From the above script, you can observe that I am calculating the entire sales of an employee (identified by the employee id as 2) and assigning the value to a variable named @EmpSales. I am using the same value to test a part of the CASE statement. But the value is checked against the average price of all orders, which is retrieved as part of the SELECT statement.
I used only a simple SELECT in the above CASE structure. Depending on your needs, you can even work with sub-queries, joins etc. on both sides of the condition!
Next: CASE as part of IF condition in T-SQL >>
More MS SQL Server Articles
More By Jagadish Chaterjee
