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.
1条答案
按热度按时间mzmfm0qo1#
If your code compiles and is correct, then the only logical reason would be that @client_Id is NULL.
Try this:
That or the query below returns no results: