October 5, 2010 at 3:08 am
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
October 5, 2010 at 3:43 am
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
October 5, 2010 at 6:23 am
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
October 5, 2010 at 7:20 am
Thanks a million Abhijeet for helping me, you're so right. It works now. Really appreciated.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply