Cannot update using the result from dynamic sql

• sqlblue

  SSCrazy

  Points: 2324

  More actions

  October 5, 2010 at 3:08 am

  #241215

  Hi, I got 2 queries below. You won't be able to run the 2nd query, but if you could just review it and let me know

  what I am doing wrong.

  use adventureworks;

  go

  declare @sql nvarchar(1000), @finalresult numeric(10,2)

  set @sql = 'set @result = 258.89;';

  exec sp_executesql @sql, @params = N'@result as numeric(10,2) output', @result = @finalresult output;

  update Sales.SalesOrderDetail

  set UnitPrice = @finalresult

  where SalesOrderID = 43659

  and SalesOrderDetailID = 1

  -- This first query run fine and I was be able to update the table fine as stated above.

  So, I am using the similiar method for the 2nd query, and I can see the result came back with

  the number when I run it, but it won't update the table with the result I got. It is really strange.

  The FieldValue column remains as NULL.

  DECLARE @sql AS NVARCHAR(1000), @FinalResult INT, @ID INT

  SET @ID = 1

  SET @sql = N'SELECT SUM(' + @Field + ') ' +

  'FROM ' + @Table + ' '

  EXEC sp_executesql @sql, @params = N'@Result AS INT OUTPUT', @Result = @FinalResult OUTPUT;

  UPDATEdbo.Table2

  SETFieldValue = @FinalResult

  WHEREID = @ID

• sqlblue

  SSCrazy

  Points: 2324

  More actions

  October 5, 2010 at 3:43 am

  #1231548

  I found that if I added another field to the update statement, it would update that new

  field fine. It just won't update the field that is using the variable, even though I can

  see the value of the variable in the result set when I run it.

  UPDATE dbo.Table2

  SET FieldValue = @FinalResult, Field2 = 'Testing'

  WHERE ID = @ID

• Abhijeet Chavan

  SSCommitted

  Points: 1511

  More actions

  October 5, 2010 at 6:23 am

  #1231620

  You need to assign the value of the sum to variable @Result

  Correct the set @sql statement to the following.

  SET @sql = N'SELECT @Result = SUM(' + @Field + ') ' +

  'FROM ' + @Table + ' '

  this will solve the problem

• sqlblue

  SSCrazy

  Points: 2324

  More actions

  October 5, 2010 at 7:20 am

  #1231674

  Thanks a million Abhijeet for helping me, you're so right. It works now. Really appreciated.