Update new column with result set from stored procedure

Question

I have a table with existing data. I have a requirement to add a new column to the table which will be populated with a value retrieved using a stored procedure. This is fine for new data I will be adding to the table.

How can I run the stored procedure for each existing row, passing in the parameters from two existing columns, and then updating the new column with the result. Effectively I want to run something like the following:

UPDATE 
  TableWithNewColumn
SET
  NewColumn = EXEC NewProcedure(TableWithNewColumn.ID, TableWithNewColumn.Code);

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

I know a scalar function would be ideal for this task but unfortunately the SP has been provided by a third party and it's the only way I can provide the data.

I suggest you use an in-line SQL expression instead of calling a proc for each row. You could encapsulate the expression in a scalar function for reusability. If you must call a stored procedure that returns a result set, you'll need to 1) create a cursor for the source query, 2) call proc for each row with result inserted into a temp table or table variable, and 3) update the table using the temp table/table variable. — Dan Guzman, Jun 18 '14 at 12:27
@DanGuzman-SQLServerMVP Thanks for that and I was thinking that a cursor would be the way to go and loop through every record. If you could provide and short example of how to do this I would really appreciate it because I haven't used a cursor in SQL Server before, only Oracle. I will research it if needs be but if you could get me started that would be a big help. — Tobsey, Jun 18 '14 at 12:31
you can't call a stored procedure from within a function. a stored procedure could modify data and functions are not allowed to do that. see this post on [SO](http://stackoverflow.com/questions/6344880/execute-stored-procedure-from-a-function) for details and hints not to be followed ^^ — Paolo, Jun 18 '14 at 12:33

score 2 · Accepted Answer · answered Jun 19 '14 at 02:44

Below is a cursor example, assuming the id column is unique. If you can change the stored proc to return an output parameter, you could use the output parameter in the update statement directly instead of inserting the proc result set into a table variable and the subquery in the update.

DECLARE
    @id int,
    @code varchar(16);

DECLARE @Result TABLE (
    ResultValue varchar(16)
    );

DECLARE TableRows CURSOR LOCAL FOR
    SELECT
        id
        ,code
    FROM dbo.TableWithNewColumn;
OPEN TableRows;
WHILE 1 = 1
BEGIN

    FETCH NEXT FROM TableRows INTO @id, @code;
    IF @@FETCH_STATUS = -1 BREAK;

    DELETE FROM @Result;
    INSERT INTO @Result EXEC dbo.NewProcedure @id, @code;

    UPDATE TableWithNewColumn
    SET NewColumn = (SELECT ResultValue FROM @Result)
    WHERE id = @id;

END;
CLOSE TableRows;
DEALLOCATE TableRows;

score 0 · Answer 2 · answered Jun 18 '14 at 13:00

Here is a thought... populate a TEMP table with the values of ID, Code and the NewColumn result. Then do an update of your table TableWithNewColumn with a join to the temp table. I suggest this because updating with all those procedure calls will be slow.

Update new column with result set from stored procedure

2 Answers2