Working wth Variables in Database Interactions with Transact-SQL - Common scenarios while retrieving data from a database into variables
(Page 3 of 4 )
In the previous examples, I included a WHERE clause in the SELECT statement to retrieve only a single row. What happens when we miss or exclude a WHERE clause? Let us consider the following example:
use Northwind
go
declare @Lname varchar(20)
declare @Fname varchar(20)
select @Lname = LastName, @Fname = FirstName
from dbo.Employees
print 'Name of the Employee:' + @Lname + ',' + @Fname
go
In the above example, you can observe that I didn't specify any WHERE clause. The SELECT statement tries to retrieve all rows from the table and push them into the same two variables (@Lname and @Fname). But a variable can have only one value at a time. If a new value is assigned to it, the old one vanishes.
In the above case, each row of values in variables gets replaced by the next row of values until the SELECT statement retrieves the last row. And finally, only the values of the last row will be available in variables!
Another scenario is the handling of no rows. What happens if no rows are retrieved? Let us consider the following example:
use Northwind
go
declare @Lname varchar(20)
declare @Fname varchar(20)
declare @EmpID int
set @EmpID = 49
select @Lname = LastName, @Fname = FirstName
from dbo.Employees
where EmployeeID = @EmpID
print 'Name of the Employee:' + @Lname + ',' + @Fname
go
There exists no employee with an Employee ID of 49 in the Northwind database. The SELECT statement will not be able to retrieve any rows and thus it simply fills NULLs into the variables. It is always a good practice to test for the NULLs also as follows:
use Northwind
go
declare @Lname varchar(20)
declare @Fname varchar(20)
declare @EmpID int
set @EmpID = 49
select @Lname = LastName, @Fname = FirstName
from dbo.Employees
where EmployeeID = @EmpID
if @Lname is null
print 'Employee not found'
else
print 'Name of the Employee:' + @Lname + ',' + @Fname
go
Using T-SQL variables efficiently
Now that we understand variables and filling database values into them, we will look into a few tricks for using them efficiently. Let us start with the following script:
use Northwind
go
declare @Lname varchar(400)
set @Lname = ''
select @Lname = @Lname + LastName + ', '
from dbo.Employees
print @Lname
go
In the above script, I declared a variable named "@Lname" and used it recursively (after initializing it). The SELECT simply retrieves every successive value and concatenates it with the value available in "@Lname," and then stores the latest value (concatenated value) into the same variable "@Lname" (adding a comma after every concatenation). The result would be nothing but the list of all employee names separated with commas!
The above script may not be good to use for listing out too many concatenated strings, as the size of a variable is limited (cursors and tables would be great choices). But it would be a good option for operations such as sum, running sum, and so forth.
Just for your information, you can display a cumulative or running sum by writing a query as follows:
SELECT OrderID, sum(UnitPrice * Quantity) Price,
(SELECT SUM(UnitPrice * Quantity)
FROM [Order Details] as b
WHERE b.orderid <= a.orderid) running_sum
FROM [Order Details] as a
group by OrderID
ORDER BY orderid
The next section will continue our discussion of the efficient use of variables.
Next: Using T-SQL variables efficiently: continued >>
More MS SQL Server Articles
More By Jagadish Chaterjee
