Updating a Stored Procedure that is using a CURSOR

I have a Stored Procedure that uses a CURSOR.  How could this be changed to not use a CURSOR?

The attached is the test code for the Stored Procedure.
DeletePDQUsers.sql
wdbatesAsked:
Who is Participating?
 
ste5anConnect With a Mentor Senior DeveloperCommented:
Caveat: You can do it without CURSOR. But this means one batch and on transaction instead of one transaction per row in the cursor.

DECLARE @Sql NVARCHAR(MAX);
DECLARE @p_UserID INT = 0;

WITH    Data
          AS ( SELECT DISTINCT
                        PDQTableName
               FROM     sPDQDATA.LTrack L
                        INNER JOIN sPDQDATA.Ements E ON L.LTID = E.LTID
                                                        AND E.UserID = @p_UserID
               WHERE    PDQTableName IS NOT NULL
             )
    SELECT  @Sql = ( SELECT 'DELETE FROM sPDQData.' + QUOTENAME(PDQTableName)
                            + ' WHERE UserID = '
                            + CAST(@p_UserID AS VARCHAR(12)) + '; '
                     FROM   Data D
    FOR     XML PATH('') ,
                TYPE
 ).value('.', 'NVARCHAR(MAX)');    

PRINT @Sql;
--EXECUTE (@Sql);

Open in new window

0
 
wdbatesAuthor Commented:
Great work!
Thank you for the quick response.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.