Using @@ROWCOUNT and TABLE Variables for Database Interactions with Transact-SQL
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 =(selectsum(unitprice * quantity) from [order details] where orderid in(select orderid from orders where employeeid=@EmpID)) set @Status =case when @EmpSales <(selectavg(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!
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 =(selectsum(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 setnocounton 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 setnocounton 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.
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:
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:
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:
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.
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:
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