CODEWITHSUNDEEP
sqlsql-server
Koosh
Koosh

Reputation: 896

stored procedure issue using fields from the #temp table

I have a stored procedure that basically does this:

Create table #temp(
field1 int,
field2 int,
total int,
total2 int,
total3 int
)
insert into #temp
(
 field1,
 field2,
 )
 Select x.field1, y.field2 from tblA x left join tblB y on x.clientid = y.clientid

This works great, this is very much simplified.

Now what I'm doing is UPDATING values for Total, Total2 and Total3 like so:

update #temp set Total = field1 + field2  <-- works
                 total2 = field1 + field2 + 300 <-- works
                 total3 = total2 * 100.0    <-- no value

So here in the update statement above, if I use the field that had already been inserted into the table, they let me UPDATE it, however, since I updated total2 in the table, I am unable to use it in total3? Any idea why?

Upvotes: 1

Views: 75

Answers (4)

Adam Murray
Adam Murray

Reputation: 33

The problem you are having is because the total2 variable is being set at the same time you are attempting to access it. In order to make the update work correctly you must update total3 with the same logic used to update total2 OR you have to create a separate update for total3.

This is how I would do it:

update #temp set Total = field1 + field2  <-- works
                 total2 = field1 + field2 + 300 <-- works
                 total3 = (field1 + field2 + 300) * 100.0    <-- should get value

Notice that I put the addition logic for total2 in the paranthesis used to calculate total3. This is because the total2 variable holds no value until the statement completes execution.

The alternative is to split the update into 2 separate statments such as this:

 update #temp set Total = field1 + field2  <-- works
                     total2 = field1 + field2 + 300 <-- works

and then

 update #temp set total3 = (field1 + field2 + 300) * 100.0

Upvotes: 1

Ankur Patel
Ankur Patel

Reputation: 1423

May be try the following:

update #temp set Total = field1 + field2
                 total2 = field1 + field2 + 300
                 total3 = (field1 + field2 + 300) * 100.0

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269563

This is your update:

update #temp
    set Total = field1 + field2  <-- works
        total2 = field1 + field2 + 300 <-- works
        total3 = total2 * 100.0    <-- no value

You have only set values for field1 and field2, so the other values are empty. You need to understand that the set clause is executed "all-at-once" not sequentially. Column references on the left of the = are the "new" record; column references on the right are the "old" record. Hence, you can solve the problem by repeat the expression.

Another way to solve the problem is to use a subquery, CTE, or apply:

update #temp
    set Total = v.new_total,
        total2 = v2.new_total2 <-- works
        total3 = v2.total2 * 100.0    <-- no value
    from #temp t cross apply
         (values (field1 + field2) ) v(new_total),
         (values (new_total + 300) ) v2(new_total2)

Upvotes: 3

Aaron Dietz
Aaron Dietz

Reputation: 10277

The totals columns are NULL at the time you run the update.

So total3 is doing this: NULL * 100.0

You can either update total3 separately, once you've populated total2:

update #temp 
set total = field1 + field2,
    total2 = field1 + field2 + 300

update #temp 
set total3 = total2 * 100.0

Or you can replace total2 with the logic that populates it:

update #temp 
set total = field1 + field2,
    total2 = field1 + field2 + 300,
    total3 = (field1 + field2 + 300) * 100.0

Upvotes: 3

Related Questions