Using @@ROWCOUNT and TABLE Variables for Database Interactions with 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!

CASE as part of IF condition in T-SQL

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.

A brief introduction to TABLE variables in T-SQL programming

Up to now, we have seen variables of standard data types like numeric, varchar, and so on. Now we are going to concentrate on TABLE variables. A TABLE type of variable is very similar to a variable which can hold a table of data (in the form of rows and columns). These table variables would exist only during the execution of script and will not physically affect existing database structures. In other words, they are completely managed in memory (and sometimes in TempDB as well).

A table variable must be defined with a structure (column specification) based on the rows it may contain. Let's take a look at a sample script which uses a TABLE variable. The following is the code:

use northwind
set nocount on
go

declare @t_emp TABLE
   (
     EmpID int,
     Ename varchar(30)
   )

insert into @t_emp(EmpID, Ename)
values (1001, 'Jagadish')
insert into @t_emp(EmpID, Ename)
values (1002, 'Chatarji')

select * from @t_emp

go

Let us try to understand the above script line by line.

declare @t_emp TABLE
   (
     EmpID int,
     Ename varchar(30)
   )

The above statement simply defines a new variable named @t_emp which contains two columns (EmpID and Ename) of storage. This table variable exists only during the execution of the script and automatically vanishes (it is erased from memory) once the script execution is complete. Going further, we have the following:

insert into @t_emp(EmpID, Ename)
values (1001, 'Jagadish')
insert into @t_emp(EmpID, Ename)
values (1002, 'Chatarji')

The above two are very similar to the traditional INSERT statements, but are focused on inserting into the @t_emp table variable. Going further, we have the following:

select * from @t_emp

Once all the rows are inserted into the table variable, I am simply displaying all the information back to the user in the form of a real table!

Primary key and Identity columns in TABLE variables

Just like database tables, we can enforce TABLE variables with Primary Key, Unique, Identity, NOT NULL etc. types of constraints. Please understand, however, that not all types of constraints will work with TABLE variables.

Let us rewrite the previous script to enforce the "primary key" constraint as follows:

use northwind
set nocount on
go

declare @t_emp TABLE
   (
     EmpID int primary key,
     Ename varchar(30)
   )

insert into @t_emp(EmpID, Ename)
values (1001, 'Jagadish')
insert into @t_emp(EmpID, Ename)
values (1001, 'Chatarji')

select * from @t_emp

go

The above script would result in an error with primary key violation, as I am trying to insert two rows with the same employee id.

We can even include identity columns (also called auto number or surrogate key columns) as part of the TABLE variable as follows:

use northwind
set nocount on
go

declare @t_emp TABLE
   (
     ID int identity(1,1),
     EmpID int primary key,
     Ename varchar(30)
   )

insert into @t_emp(EmpID, Ename)
values (1001, 'Jagadish')
insert into @t_emp(EmpID, Ename)
values (1002, 'Chatarji')

select * from @t_emp

go

The output for the above script would result in three columns, where the first column would contain sequentially generated values starting with 1 and incrementing by 1.

Filling a TABLE variable with existing rows

We are not allowed to use the SELECT..INTO statement to work with TABLE variables. To transfer the rows from a SELECT to a TABLE variable, the only option we have is to use INSERT with SELECT. The following is a sample script:

use northwind
set nocount on
go

declare @t_emp TABLE
   (
     ID int identity(1,1)primary key,
     EmpID int unique,
     Ename varchar(30) not null
   )

insert into @t_emp(EmpID, Ename)
select EmployeeID, LastName + ' ' + FirstName
from Employees
select * from @t_emp

go

The most important statement of the above script is the following:

insert into @t_emp(EmpID, Ename)
   select EmployeeID, LastName + ' ' + FirstName
   from Employees

It clearly states that the output of the SELECT statement should be transferred to the table variable @t_emp. But while transferring we have to make sure that the number of columns along with data types should match.

In the next upcoming article, I shall concentrate more on tables, loops, cursors, and more. 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