Pull results from a stored procedure

1

I've got a table of items that 77,000 item_id 's. The ERP system has a convoluted way to calculate the unit price based on customer id, price library, price book and price page.
They've written a stored procedure that calculates the unit price. I used a SQL trace to grab the stored procedure call the web app is used. I've look at the stored procedure, and it's about 300 pages long. Far too long for me to reverse engineer what they've done. I need to find a way to get the unit price for each item_id.

This would give the item_id's
select item_id from inventory

I'd like to run something like this

select item_id, (select unit_price from exec pricelib @item_id=item_id) as unit_price from inventory

But of course that does not work on sql server 2019.

The stored procedure only gets a single price, based off a single item_id.

Anyone have any ideas?

2

Write your own proc that cursors thru all item_ids (up to a few thousand should be OK) and calls the standard proc and stores the results for each item_id.

Btw, you should include the schema name when running a proc, like "EXEC dbo.pricelib" (or whatever schema it is if not dbo)

3

using cursors as Scott mention will works but it is not performant. If performance is a concern, you have no choice but to change pricelib (or make a copy of pricelib to another SP) to handle multiple item_id