Working wth Variables in Database Interactions with Transact-SQL

Quite some time ago, I started a lengthy series on "Database Interactions with PL/SQL." It continues to this day. I have received a lot of feedback from several readers throughout the world. I want to thank all of them for suggesting new topics for me to cover.

You can find the series here. I’ve received many requests for more information about Transact-SQL programming, but I waited until now to write more because I wanted to upgrade to the latest Microsoft SQL Server 2005 programming first. And now, I am contributing another huge series (though the grouping or titles could be different) totally dedicated to Transact-SQL programming, beginning with the basics and proceeding through the latest and advanced features.

I am assuming that the readers of this series will have some knowledge of RDBMS, SQL and some very fundamental grounding in Transact-SQL (T-SQL) such as data types, variable declarations, control structures (such as conditional statements and loops), executing scripts and so forth. I am trying to keep all of the articles of this series as simple as possible to give beginners a very good start. All of the examples in this series can be executed on the fly just by using copy and paste (unless otherwise specified).

For simplicity, I am still using the classic Northwind database. For installation instructions, you can check out http://www.totaltraining.com/supt/pdf/NorthwindDB.pdf. You can download and attach it for free.

In this article, I shall introduce you to retrieving information from a SQL server database using Transact-SQL variables. 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 or previous editions (as I am concentrating on Microsoft SQL Server 2005). If you have any problems with executing the code, please post in the discussion area.

{mospagebreak title=Retrieving data from a database using a T-SQL script}

Let us consider the following example:

use Northwind
go

declare @EmpID int
set @EmpID = 4

select LastName + ‘,’ + FirstName
from dbo.Employees
where EmployeeID = @EmpID

go

In the above script, I am asking the script to work within the context of the  "Northwind" database. If you are already connected to the same database, this may not be necessary. But it is always a good practice to include the "use" statement to switch to the respective database context you require.

The word "go," in simple terms, executes all the previous statement(s) immediately as a "batch." Going further down, we have the following:

declare @EmpID int
set @EmpID = 4

The above two statements simply declare and initialize a new variable named "@EmpID" (of type "int") with a value of 4. Every variable you use in the script must be declared prior to its usage and every variable must be preceded with the "@" symbol. Going further down, we have the following:

select LastName + ‘,’ + FirstName
from dbo.Employees
where EmployeeID = @EmpID

The above SELECT statement simply retrieves the name (combined value of LastName and FirstName separated with comma) from the Employees table based on the EmployeeID available in the variable @EmpID.

Retrieving data from a database into variables using a T-SQL script

In the previous example, we directly used the SELECT statement to retrieve and display the information to the user. In general, when we are working with scripts, we may need to retrieve the values of the SELECT statement into variables and use them extensively as part of the entire script.

Let us consider the following example:

use Northwind
go
declare @ename varchar(20)
declare @EmpID int
set @EmpID = 4

select @ename = LastName + ‘,’ + FirstName
from dbo.Employees
where EmployeeID = @EmpID

print ‘Name of the Employee:’ + @ename
go

In the above example, I declared one more variable, "@ename" of type string. You can also observe that it is not initialized directly as we did for "@EmpID." The following is the statement which does it.

select @ename = LastName + ‘,’ + FirstName
from dbo.Employees
where EmployeeID = @EmpID

The above SELECT statement retrieves the name (combined LastName and FirstName) of the employee and assigns the value to the variable "@ename." When the SELECT statement assigns its values to the variables, it will not display any output to the user. So, we need another statement to display the value back to the user as follows:

print ‘Name of the Employee:’ + @ename

The above statement displays a message concatenated by the employee name retrieved. You can also work with more than one column as follows:

use Northwind
go
declare @Lname varchar(20)
declare @Fname varchar(20)
declare @EmpID int

set @EmpID = 4
select @Lname = LastName, @Fname = FirstName
from dbo.Employees
where EmployeeID = @EmpID

print ‘Name of the Employee:’ + @Lname + ‘,’ + @Fname
go

{mospagebreak title=Common scenarios while retrieving data from a database into variables}

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.

{mospagebreak title=Using T-SQL variables efficiently: continued}

We all know that finding the sum of values is very easy using the aggregate SUM as follows:

SELECT sum(UnitPrice * Quantity)
FROM [Order Details]

We can retrieve the sum in to a variable in a script as follows:

use Northwind
go
declare @sum numeric(10,2)

SELECT @sum = sum(UnitPrice * Quantity)
FROM [Order Details]

print @sum
go

The same can be achieved by further simplifying it as follows:

use Northwind
go
declare @sum numeric(10,2)

SET @sum = (SELECT sum(UnitPrice * Quantity)
FROM [Order Details])

print @sum
go

We can also use the recursive method as follows:

use Northwind
go
declare @RunningSum numeric(10,2)
set @RunningSum = 0.0

SELECT @RunningSum = @RunningSum + sum(UnitPrice * Quantity)
FROM [Order Details] as a

print @RunningSum
go

In the next article, I shall concentrate a bit more on variables, conditions, loops, DML statements and so forth. 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.

2 thoughts on “Working wth Variables in Database Interactions with Transact-SQL

  1. Hello guys,

    This is the first in a series of articles focusing on Transact-SQL programming. This contribution simply gives details about variables in Transact-SQL.

    enjoy,
    Jag

[gp-comments width="770" linklove="off" ]