Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

Nakul Vachhrajani is a Technical Specialist & Systems development professional with iGATE. He holds a MCTS (SQL Server 2008: Implementation & Maintenance)
Browse by Tags · View All
#SQLServer 305
SQL Server 304
Administration 252
DBA 241
T-SQL 234
#TSQL 232
Development 226
Tips 216
Guidance 148
Best Practices 119

Archive · View All
April 2011 14
March 2012 11
December 2011 11
March 2011 11
December 2012 10
October 2011 10
January 2011 10
September 2013 9
January 2013 9
November 2012 9

#0336 - SQL Server - Underappreciated Features - Using the UPDATE clause to update the value of a variable

May 5 2014 12:00AM by Nakul Vachhrajani   

We typically use the UPDATE clause to update or change the existing data in a given table or view. Most business logic requires that the value of one or more variables is set based on this update. Generally, we find that these requirements are realized with two statements – an UPDATE, followed by a SELECT statement (or vice versa, as the case may be). However, a little known and therefore underappreciated feature of the UPDATE clause is that it can also be used to update variables. Here’s an example.

Assume that we have a requirement to update the order detail quantities in the [Sales].[SalesOrderDetail] table of the [AdventureWorks2012] database with a given value supplied in the variable @orderDetailQuantityIncrementBy. At the same time, we also need to know the amount by which the order value increased or decreased.

A typical implementation of this would be:

USE AdventureWorks2012;
GO

--Variable indicating the change in Order Quantity
DECLARE @orderDetailQuantityIncrementBy INT = 1;

--Temporary table variable to hold the before/after values
DECLARE @orderUpdate TABLE (BeforeOrderQuantity INT,
                            BeforeUnitPrice DECIMAL(20, 8),
                            AfterOrderQuantity INT,
                            AfterUnitPrice DECIMAL(20, 8)
                           );

--Using a transaction so that we can return to the table's base state
BEGIN TRANSACTION UpdateVariableTest;

  --Perform the update
  UPDATE so
  SET so.OrderQty += @orderDetailQuantityIncrementBy
  OUTPUT deleted.OrderQty, 
         deleted.UnitPrice, 
         inserted.OrderQty, 
         inserted.UnitPrice
  INTO @orderUpdate (BeforeOrderQuantity, 
                     BeforeUnitPrice, 
                     AfterOrderQuantity, 
                     AfterUnitPrice)
  FROM Sales.SalesOrderDetail AS so
  WHERE so.SalesOrderID = 43659
    AND so.SalesOrderDetailID IN (8, 10);

  --Calculate the change in order total
  SELECT SUM((ou.AfterOrderQuantity * ou.AfterUnitPrice)
              - (ou.BeforeOrderQuantity * ou.BeforeUnitPrice)
            ) AS orderAmountChanges
  FROM @orderUpdate AS ou;

ROLLBACK TRANSACTION UpdateVariableTest;
GO

/***********
  RESULTS
***********/
--orderAmountChanges
--------------------
--34.54040000

As you can see, we used an UPDATE clause and collected the before/after update information in a temporary table variable. Later, we performed arithmetic operations on the value stored in the table variable.

Using a single UPDATE clause to update a variable

This requirement can be realized within a single UPDATE clause also. Here’s the modified solution:

USE AdventureWorks2012;
GO

--Variable indicating the change in Order Quantity
DECLARE @orderDetailQuantityIncrementBy INT = 1;

--Variable indicating the change in Order Amount
DECLARE @orderAmountChanges DECIMAL(20,8) = 0;

--Using a transaction so that we can return to the table's base state
BEGIN TRANSACTION UpdateVariableTest;
    --Perform the update
    UPDATE so
    SET so.OrderQty += @orderDetailQuantityIncrementBy,
        @orderAmountChanges += (@orderDetailQuantityIncrementBy * so.UnitPrice) 
    FROM Sales.SalesOrderDetail AS so
    WHERE so.SalesOrderID = 43659
      AND so.SalesOrderDetailID IN (8, 10);

    --Fetch the value from the variable
    SELECT @orderAmountChanges AS OrderAmountChanges;

ROLLBACK TRANSACTION UpdateVariableTest;
GO
/***********
  RESULTS
***********/
--OrderAmountChanges
--------------------
--34.54040000

As can be seen from the example above, the UPDATE clause makes realization of the said requirement very simple & readable.

Further Reading

  • Compound Operators – the most common mistake [Link]
  • The UPDATE clause [Books ON Line Link]

Until we meet next time,

Be courteous. Drive responsibly.

Tags: #SQLServer, SQL Server, T-SQL, #TSQL, Development, Tips


Nakul Vachhrajani
4 · 36% · 11648
2
 
0
Lifesaver
 
 
 
0
Incorrect



Submit

Your Comment


Sign Up or Login to post a comment.

"#0336 - SQL Server - Underappreciated Features - Using the UPDATE clause to update the value of a variable" rated 5 out of 5 by 2 readers
#0336 - SQL Server - Underappreciated Features - Using the UPDATE clause to update the value of a variable , 5.0 out of 5 based on 2 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]