CODEWITHSUNDEEP
sqlsql-serverstored-proceduresdatabase-cursor
pandasman
pandasman

Reputation: 90

Execute a stored procedure for each row inside a table for each distinct UID

I have a table of with a Unique ID and a set number of columns as seen below. Each row in the table has a UID and then distinct values in each column. The stored procedure takes each of these values (including the UID) and runs a multitude of other code.

To run this it would be:

EXEC stored_proc @UID @Col1 @Col2 etc

Each UID must run at a different time and each row for said UID needs to be loaded at the same time. Once loaded the script needs to move to the next UID.

The most obvious approach to this is via a cursor but I have had no real progress. I have attempted this via a cursor that uses a temporary table of only the UID and inner joined this for each row but sadly it did not work.

Sample table (actual table: circa 5000 rows):

UID Version Site QuestionOI GeneralAnswer
212109191425-01 6 AGBNL 4 GENANSWER1
212109191425-01 6 AGBNL 9 NULL
212109191425-01 6 AGBNL 100 NULL
212109191425-01 6 AGBNL 103 NULL
212109191425-03 6 AGFCA 11 NULL
212109191425-03 6 AGFCA 22 NULL
212109191425-05 6 COBAR 4 GENANSWER2
212109191425-05 6 COBAR 9 NULL
212109191425-05 6 COBAR 13 NULL
212109191425-05 6 COBAR 15 NULL

Can anyone think of an alternative approach/way to iteratively loop through each UID then run a stored procedure on each row for the UID? Use of a cursor is not essential.

Any help would be greatly appreciated and an award will be given for a clean and most importantly quick processing speed (note the nested SP takes little time to run)

Thanks!

Heres a sample of some code I tried as requested, it seemed to loop through each of the lines rather than UID then row. The stored procedure would run instead of the second print statement Note I've had to alter it a bit for sensitivity reasons.

Declare @ClientID VARCHAR;
Declare @UID VARCHAR;

DECLARE Cur1 CURSOR FOR
    SELECT DISTINCT Site FROM #InsertTable

OPEN Cur1
FETCH NEXT FROM Cur1 INTO @ClientID;
WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT 'Processing ClientID: ' + @ClientID;
    DECLARE Cur2 CURSOR FOR
        SELECT UID FROM #InsertTable Where Site=@ClientID;
    OPEN Cur2;
    FETCH NEXT FROM Cur2 INTO @UID;
    WHILE @@FETCH_STATUS = 0
    BEGIN
        PRINT 'Found UID: ' + Cast(@UID as Varchar);
        FETCH NEXT FROM Cur2 INTO @UID;
    END;
    CLOSE Cur2;
    DEALLOCATE Cur2;
    FETCH NEXT FROM Cur1 INTO @ClientID;
END;
PRINT 'DONE';
CLOSE Cur1;
DEALLOCATE Cur1;

Upvotes: 0

Views: 3060

Answers (1)

SOS
SOS

Reputation: 6550

A cursor would work and is the only way I know of to execute a procedure on each row individually. That said, it is not the most optimal approach. A cursor is almost always going to be slower than a set based approach. You might look into whether it could be refactored so you can avoid the cursor and row by row overhead.

 DECLARE @YourCursor CURSOR 
    , @UID VARCHAR(50)
    , @Version INT  
    , @Site VARCHAR(10)
    , @QuestionOI INT   
    , @GeneralAnswer VARCHAR(10);

SET @YourCursor = CURSOR FOR
    SELECT [UID]
        , [Version] 
        , [Site] 
        , QuestionOI 
        , GeneralAnswer
    FROM YourTable
    ORDER BY [UID];

OPEN @YourCursor;  

--- load first row           
FETCH NEXT FROM @YourCursor 
INTO @UID, @Version, @Site, @QuestionOI, @GeneralAnswer 

WHILE @@FETCH_STATUS = 0  
BEGIN  
        --- execute proc 
        EXEC stored_proc @UID, @Version, @Site, other variables...
      
        --- get next row           
        FETCH NEXT FROM @YourCursor 
        INTO @UID, @Version, @Site, @QuestionOI, @GeneralAnswer 
END;  
CLOSE @YourCursor;  
DEALLOCATE @YourCursor;  
GO

Upvotes: 2

Related Questions