SQL Server: Nested cursor missing first row in the inner cursor

Question

I went through the Microsoft documentation and implemented the nested cursor,but somehow it is missing the first row of the inner cursor. Below is the query to review.

DECLARE @client_id VARCHAR(50);
DECLARE @reportID INT;
DECLARE @report_name VARCHAR(250);
DECLARE @client_report_id INT;
DECLARE @pdf_file_format VARCHAR(200) = '';
DECLARE @expected_file_format VARCHAR(200) = 'somevalue';
DECLARE @export_file_name VARCHAR(200) = '';


IF OBJECT_ID('tempdb..#client_reports') IS NOT NULL DROP TABLE #client_reports;
--Create a temp table to hold client_Report data.
SELECT cr.client_report_id, cr.client_id, c.name 'client_name',r.report_id, r.name 'report_name'
        ,r.report_code, c.export_file_name, cr.pdf_file_format 
INTO #client_reports
FROM t004_client_report cr 
JOIN t002_report r ON r.report_id = cr.report_id
JOIN  t001_client c ON cr.client_id = c.client_id
WHERE r.name LIKE ('FilterText%')

DECLARE cursor_ReportName CURSOR FOR 
SELECT distinct report_id, report_name 
FROM #client_reports; 

OPEN cursor_ReportName

FETCH NEXT FROM cursor_ReportName 
INTO @reportID, @report_name

WHILE @@FETCH_STATUS = 0 
BEGIN
    PRINT '------------------'
    PRINT 'Processing report: ' + @report_name
    PRINT '------------------'

    DECLARE cursor_ClientReport CURSOR FOR
    SELECT client_id, client_report_id, export_file_name, pdf_file_format
    FROM #client_reports
    WHERE report_id = @reportID 

    OPEN cursor_ClientReport

    FETCH NEXT FROM cursor_ClientReport 
    INTO @client_id, @client_report_id, @export_file_name, @pdf_file_format
    
    WHILE @@FETCH_STATUS = 0
    BEGIN
        -- Few Rows are not processing here due to some issue
        -- Write update code here for the client report pdf file format
        IF @pdf_file_format <> @expected_file_format
        BEGIN
            PRINT @client_id + ': updating pdf_file_format from: ' + @pdf_file_format + ' to ' + @expected_file_format
            --Some Update logic here    
        END

        FETCH NEXT FROM cursor_ClientReport 
        INTO @client_id, @client_report_id, @export_file_name, @pdf_file_format
    END
    
    CLOSE cursor_ClientReport--Close the cursor and deallocate.
    DEALLOCATE cursor_ClientReport
    -- Get the next report.  
    FETCH NEXT FROM cursor_ReportName 
    INTO @reportID, @report_name
END
CLOSE cursor_ReportName;--Close the curson and deallocate.
DEALLOCATE cursor_ReportName;

IF OBJECT_ID('tempdb..#client_reports') IS NOT NULL DROP TABLE #client_reports;

Am I missing something related to the implementation from the documentation??

I found the reason of the error after reviewing Ross Bush's comment. One of the column values was NULL and that was causing problem. I have modified the code as below and it is working as charm.

Your code is "lying". SELECT distinct report_id, report_name FROM #client_reports; neither of these columns exist in #client_reports according to your snippet. — siggemannen, Mar 14 '23 at 18:07
@siggemannen: I took few code out of the query to share it here. Now it has. — Niranjan Singh, Mar 14 '23 at 18:11
I suppose the real question is why are you using a `CURSOR` in the first place? — Thom A, Mar 14 '23 at 18:31
it still doesn't compile because you have some misplaced quote. I don't see anything wrong with your cursor on a quick glance, so unless you can't put the whole code here, i can't help you — siggemannen, Mar 14 '23 at 18:48
I fixed your code and it works fine: https://dbfiddle.uk/ZtRIsmtV probably your data is screwed somehow — siggemannen, Mar 14 '23 at 18:55
@siggemannen, Thanks for suggesting the change. I have not put the entire code at that time. Now it is the entire code that runs without any error. — Niranjan Singh, Mar 15 '23 at 04:32

Ross Bush · Accepted Answer · 2023-03-14T18:29:10.880

1

If your code compiles and is correct, then the only logical reason would be that @client_Id is NULL.

Try this:

--PRINT 'client:'+ @client_id 
PRINT ISNULL(@client_id, '@client_id IS NULL')

That or the query below returns no results:

SELECT client_id, client_report_id
FROM #client_reports
WHERE report_id = @reportID

edited Mar 14 '23 at 18:29

answered Mar 14 '23 at 18:18

Ross Bush

14,648
2
32
55

SQL Server: Nested cursor missing first row in the inner cursor

1 Answers1