How to Use Variables, IF and CASE in Database Interactions with TransactSQL
(Page 1 of 5 )
This is the second article in a series focused on programming with Transact-SQL. In this article, I shall cover a few of the tricks for using variables and finally touch on IF and CASE structures as part of 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 (as I am concentrating on Microsoft SQL Server 2005). If you have any problems with executing the code, please post in the discussion area.
Using variables with the UPDATE command
In my previous article, I introduced variables and their usage in T-SQL scripts. Now, I shall extend the concept of variables to the UPDATE statement. Let us consider the following script:
use northwind
go
set nocount on
go
declare @LatestValue money
update [Order Details]
set UnitPrice = UnitPrice + 2.5
where OrderID = 10248
and ProductID = 11
set @LatestValue = (select UnitPrice from [order details]
where OrderID = 10248
and ProductID = 11)
print 'Updated Value: ' + convert(varchar,@LatestValue)
go
Whenever a DML statement is executed, the output includes the number of rows affected by the DML statement. To suppress that message we can use "set nocount on." According to the above script, I declared a variable, @LatestValue, which should contain the updated unit price for the respective order and product.
In the above script, the UPDATE statement modifies (or increments) the unit price. As the UPDATE would not return any value, I used a separate SELECT statement to retrieve the updated value and finally assigned the same to the @LatestValue variable.
Instead of using two separate statements (UPDATE and SELECT), we can achieve the same using a single UPDATE statement as follows:
use northwind
go
set nocount on
go
declare @LatestValue money
update [Order Details]
set @LatestValue = UnitPrice = UnitPrice + 2.5
where OrderID = 10248
and ProductID = 11
print 'Updated Value: ' + convert(varchar,@LatestValue)
go
In the above UPDATE statement, the modified unit price (unit price + 2.5) gets assigned to both a row (for the column UnitPrice) and a variable (@LatestValue) at the same time!
Next: Generating serial numbers (or sequences) and cumulative sums using the UPDATE statement >>
More MS SQL Server Articles
More By Jagadish Chaterjee
/ 39 
