?
Solved

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

Posted on 2008-11-19
4
Medium Priority
?
759 Views
Last Modified: 2012-05-05
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

0
Comment
Question by:hdbishopjr
4 Comments
 
LVL 18

Expert Comment

by:jmoss111
ID: 22999704
0
 
LVL 26

Expert Comment

by:tigin44
ID: 22999705
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

0
 
LVL 9

Accepted Solution

by:
Ernariash earned 2000 total points
ID: 22999712
Cursor could do the work.. please see Attached Code Snippet: thanks.

	declare @SAPDeliveryNo nvarchar(255)
	
	declare CustList cursor for
		SELECT SAPDeliveryNo FROM blending_orders
		WHERE OrderStatus = 'P' OR [OrderStatus] = 'R'  
	OPEN CustList
	FETCH NEXT FROM CustList 
	INTO @SAPDeliveryNo
	
	WHILE @@FETCH_STATUS = 0
	BEGIN
			-- 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
	  FETCH NEXT FROM CustList INTO @SAPDeliveryNo
	END
	CLOSE CustList
	DEALLOCATE CustList

Open in new window

0
 

Author Closing Comment

by:hdbishopjr
ID: 31518515
Thanks so much, you saved me many hours.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

612 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question