sql - Update new column with result set from stored procedure -

sql - Update new column with result set from stored procedure -

i have table existing data. have requirement add together new column table populated value retrieved using stored procedure. fine new info adding table.

how can run stored procedure each existing row, passing in parameters 2 existing columns, , updating new column result. want run following:

update tablewithnewcolumn set newcolumn = exec newprocedure(tablewithnewcolumn.id, tablewithnewcolumn.code);

see fiddle here: http://www.sqlfiddle.com/#!3/b0625/1

i know scalar function ideal task unfortunately sp has been provided 3rd party , it's way can provide data.

below cursor example, assuming id column unique. if can alter stored proc homecoming output parameter, utilize output parameter in update statement straight instead of inserting proc result set table variable , subquery in update.

declare @id int, @code varchar(16); declare @result table ( resultvalue varchar(16) ); declare tablerows cursor local select id ,code dbo.tablewithnewcolumn; open tablerows; while 1 = 1 begin fetch next tablerows @id, @code; if @@fetch_status = -1 break; delete @result; insert @result exec dbo.newprocedure @id, @code; update tablewithnewcolumn set newcolumn = (select resultvalue @result) id = @id; end; close tablerows; deallocate tablerows;

sql sql-server