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

oknwwptz  于 2023-03-17  发布在  SQL Server
关注(0)|答案(1)|浏览(124)

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.

mzmfm0qo

mzmfm0qo1#

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

相关问题