asked on

How do I run SQL exec sp for each record in table

I have a stored procedure I only need to run on certain records collected from my select command. How can I loop through my selection and execute my stored procedure.

-- Get SAP Delivery Number for record updates
SELECT @SAPDeliveryNo = SAPDeliveryNo
FROM blending_orders     
WHERE OrderStatus = 'P' OR [OrderStatus] = 'R'  
 
-- Hold mode : Update local record
if @status = 1 
begin
-- HOW DO I RUN THIS EXEC FOR EACH REORD IN MY SELECTION
exec @retcode6 = Sp_blending_orders_local_status_hold @SAPDeliveryNo 	
select 'Hold Mode' = @retcode6
end

Open in new window

jmoss111

You find find this article of some use:

http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1339242,00.html?track=sy200

tigin44

you loop through the records you selected by using a cursor. i.e.

-- Get SAP Delivery Number for record updates
CURSOR cSelect FOR
      SELECT SAPDeliveryNo
      FROM blending_orders
      WHERE OrderStatus = 'P' OR [OrderStatus] = 'R'

-- Hold mode : Update local record
if @status = 1
begin
      OPEN cSelect;
      FETCH NEXT FROM cSelect INTO @SAPDeliveryNo;
      WHILE (@@FETCH_STATUS <> 0)
      BEGIN
            exec @retcode6 = Sp_blending_orders_local_status_hold @SAPDeliveryNo ;
            select 'Hold Mode' = @retcode6;

            FETCH NEXT FROM cSelect INTO @SAPDeliveryNo;
      END;
      CLOSE cSelect;
      DEALLOCATE cSelect;

-- HOW DO I RUN THIS EXEC FOR EACH REORD IN MY SELECTION
end;

Regards

ASKER CERTIFIED SOLUTION

Ernariash

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

hdbishopjr

ASKER

Thanks so much, you saved me many hours.