CODEWITHSUNDEEP
sqlsql-server
Andrew
Andrew

Reputation: 1594

Update from stored procedure return

I have a stored procedure that I want to run on every row in a table that matches a where clause, the procedure already exists on the server and is used in other places so it cannot be modified for these changes.

The stored procedure returns a scalar value, I need to store this value in a column in the table, I've tried using the update:

UPDATE tbl SET tbl.Quantity = 
EXEC checkQuantity @ProductID = tbl.ProductID, @Quantity = tbl.Quantity
FROM orders tbl WHERE orderNumber = @orderNumber

But this of course doesn't work, is there a way to do this without multiple queries, reading the line info, running the proc in a loop then updating the original line?

Upvotes: 0

Views: 62

Answers (1)

Tab Alleman
Tab Alleman

Reputation: 31795

No there is no way to do this without multiple queries. This is one of the few scenarios where a cursor or loop is necessary.

Unless you can replace your stored procedure with a user-defined function, which can be run in the context of a single query.

Upvotes: 1

Related Questions