CODEWITHSUNDEEP
sqlsql-server
Tobsey
Tobsey

Reputation: 3400

Update new column with result set from stored procedure

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.

Upvotes: 1

Views: 7455

Answers (2)

Dan Guzman
Dan Guzman

Reputation: 46202

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;

Upvotes: 2

Chet
Chet

Reputation: 11

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.

Upvotes: 0

Related Questions