The there are number of tasks where variables can be used in Transact SQL in “SELECT” or “UPDATE” statements along with the table field names. The mixing field names and variables in the same “UPDATE” statement can cause unpredictable results unless you know the rules of query execution.
For example we’ll create a temporary table:
The result is the temporary table with some sample data:
How you would update the balance column?
The classic way is to create a cursor for UPDATE. Fetch each row and perform positional updates.
There is a more elegant (and efficient) way of getting the same result by using the variables in the body of the update statement:
Here is a result:
The other common task is to convert table values into a comma delimited string.
Here is a quick and dirty solution:
The result is: @BalanceCsv = ‘$10.00, $20.00, $30.00, $20.00, $10.00, $0.00,’
The conclusion is that variable in Transact DML statement can be very efficient for coding and execution.