iterative executing stored procedure with a set based approach

Question

I have an issue where I am trying to replace the following code with a different solution. Currently I am using a cursor but it is running to slowly. I am under the understanding that iterative solutions can only be completed with cursors or while loops but I am trying to find a set based approach and running out of ideas. I was hoping that I could find some inspiration here. Thanks all.

--used to find a unique list of Some_ID
@Id1, @Id2, @Id3

DECLARE SomeCursor CURSOR FOR
            SELECT SOME_ID FROM SomeTable 
                  WHERE ID1=@Id1 AND ID2=@Id2 and ID3=@Id3

    OPEN SomeCursor
          FETCH NEXT FROM SomeCursor INTO @SomeID
    WHILE @@Fetch_Status = 0      
    BEGIN

        Print @SomeID

        --simply populates a single table with values pulled from 
        --other tables in the database based on the give parameters.
        EXEC SP_PART1 @SomeID, @parameters...
        print 'part 2  starting'
        EXEC SP_PART2 @SomeID, @parameters...

        FETCH NEXT FROM SomeCursor INTO @SomeID

        print getdate()

    END 
    CLOSE  SomeCursor;
    DEALLOCATE SomeCursor;

Without knowing what ` EXEC SP_PART1` and ` EXEC SP_PART2` do impossible to answer. — Martin Smith, Mar 23 '15 at 20:13

score 0 · Answer 1 · answered Mar 23 '15 at 20:13

Your only option to make this set-based is to rewrite the sps to make them set-based (using table-valed parameters intead of individual ones) or to write set based code in this proc instead of re-using procs designed for single record use. This is a case where code re-use is usually not appropriate.

score 0 · Answer 2 · answered Mar 23 '15 at 20:16

I'm not too sure what you want, but why not use your select statement to create your sql scripts and execute them all at once with something like this.

DECLARE @sql VARCHAR(MAX);

SELECT @sql = COALESCE(@sql,'') + 'EXEC SP_Part1 ' + SOME_ID  + '; EXEC SP_Part2 ' + SomeID + '; GO '
FROM SomeTable 
WHERE ID1=@Id1 AND ID2=@Id2 and ID3=@Id3

EXEC (@sql)

iterative executing stored procedure with a set based approach

2 Answers2