CODEWITHSUNDEEP
sqlsql-serversql-server-2008stored-procedurescursor
Tyler Jones
Tyler Jones

Reputation: 424

Executing a stored procedure on every row in a SQL Server Table

So I've spent quite some time around Stack Overflow looking to make this bit of SQL that will populate a field in a view the amount of work days a project has been late.

The view joins data from two local tables on MS SQL Server (2008), and I've already written the stored procedure to calculate the work days in between two days, called by:

EXECUTE spWorkDaysLate '01/01/20XX', '01/02/20XX'

However I am not sure how to loop through each record in the view, as this needs to be done for every entry in the table. Pertinent fields in my table are startDate, endDate, and I would populate the final field (daysLate) with the result from the stored procedure above, using endDate and startDate as parameters. I found some recommendations online for using the cursor command to loop through the table, this would be the SQL I would want to run:

DECLARE @MyCursor CURSOR;
DECLARE @MyField int;
SET @MyField = 'daysLate' /* Do need to do this, or is the an argument I pass? */

BEGIN
    SET @MyCursor = CURSOR FOR
    select top 1000 daysLate from dbo.vQualityControl


    OPEN @MyCursor 
    FETCH NEXT FROM @MyCursor 
    INTO @MyField

    WHILE @@FETCH_STATUS = 0
    BEGIN
      /*
         YOUR ALGORITHM GOES HERE   
      */

      FETCH NEXT FROM @MyCursor 
      INTO @MyField 
    END; 

    CLOSE @MyCursor ;
    DEALLOCATE @MyCursor;
END;

I'm fairly new to SQL and so I know the pseudo code that I want to run is something like a for each loop, which in my mind I have looking like: 

WHILE @@FETCH_STATUS = 0
    BEGIN
      /* Bad code */
      daysLate.Value = EXECUTE spWorkDaysLate @startDate, @endDate;

      FETCH NEXT FROM @MyCursor 
      INTO @MyField 
    END;

I know thats syntactically wrong, so what should I place in the loop so that the field 'daysLate' gets populated with the result of the called stored procedure?

Upvotes: 0

Views: 1501

Answers (1)

Nam Doan
Nam Doan

Reputation: 51

Did you ever try FUNCTION, you can calculate the work days in between two days in your FUNCTION. 

SELECT *, dbo.fn_WorkDaysLate (startDate, endDate) AS DaysLate FROM dbo.vQualityControl
--OR
UPDATE A
    SET A.DaysLate = dbo.fn_WorkDaysLate (A.startDate, A.endDate)
FROM dbo.vQualityControl A

Upvotes: 1

Related Questions