Why to get error in SQL Server While Loop?-CodePudding

I am stuck with a nonsense error when running the below loop query. The thing is when I remove 'sum(case when...' lines it works fine. I have searched a lot but couldn't find any solution. I appreciate any help on this. Thanks in advance.

The error I got: Incorrect syntax near 'unit_'.:

DECLARE @Interval_List as TABLE (index_1 int, Interval VARCHAR(50), From_date date, To_date date)
INSERT INTO @Interval_List VALUES (1,  '2021_Q1',   '2021-01-01', '2021-03-31')
INSERT INTO @Interval_List VALUES (2,  '2021_Q2',   '2021-04-01', '2021-06-30')
INSERT INTO @Interval_List VALUES (3,  '2021_Q3',   '2021-07-01', '2021-09-30')
INSERT INTO @Interval_List VALUES (4,  '2021_Q4',   '2021-10-01', '2021-12-31')
INSERT INTO @Interval_List VALUES (5,  '2021_H1',   '2021-01-01', '2021-06-30')
INSERT INTO @Interval_List VALUES (6,  '2021_H2',   '2021-07-01', '2021-12-31')
INSERT INTO @Interval_List VALUES (7,  '2021',      '2021-07-01', '2021-12-31')

INSERT INTO @Interval_List VALUES (8,  '2022_Q1',   '2022-01-01', '2022-03-31')
INSERT INTO @Interval_List VALUES (9,  '2022_Q2',   '2022-04-01', '2022-06-30')
INSERT INTO @Interval_List VALUES (10, '2022_Q3',   '2022-07-01', '2022-09-30')
INSERT INTO @Interval_List VALUES (11, '2022_Q4',   '2022-10-01', '2022-12-31')
INSERT INTO @Interval_List VALUES (12, '2022_H1',   '2022-01-01', '2022-06-30')
INSERT INTO @Interval_List VALUES (13, '2022_H2',   '2022-07-01', '2022-12-31')
INSERT INTO @Interval_List VALUES (14, '2022',      '2022-01-01', '2022-12-31')

INSERT INTO @Interval_List VALUES (15,  '2023_Q1',  '2023-01-01', '2023-03-31')
INSERT INTO @Interval_List VALUES (16,  '2023_Q2',  '2023-04-01', '2023-06-30')
INSERT INTO @Interval_List VALUES (17,  '2023_Q3',  '2023-07-01', '2023-09-30')
INSERT INTO @Interval_List VALUES (18,  '2023_Q4',  '2023-10-01', '2023-12-31')
INSERT INTO @Interval_List VALUES (19,  '2023_H1',  '2023-01-01', '2023-06-30')
INSERT INTO @Interval_List VALUES (20,  '2023_H2',  '2023-07-01', '2023-12-31')
INSERT INTO @Interval_List VALUES (21,  '2023',     '2023-01-01', '2023-12-31')

INSERT INTO @Interval_List VALUES (22,  '2024_Q1',  '2024-01-01', '2024-03-31')
INSERT INTO @Interval_List VALUES (23,  '2024_Q2',  '2024-04-01', '2024-06-30')
INSERT INTO @Interval_List VALUES (24,  '2024_Q3',  '2024-07-01', '2024-09-30')
INSERT INTO @Interval_List VALUES (25,  '2024_Q4',  '2024-10-01', '2024-12-31')
INSERT INTO @Interval_List VALUES (26,  '2024_H1',  '2024-01-01', '2024-06-30')
INSERT INTO @Interval_List VALUES (27,  '2024_H2',  '2024-07-01', '2024-12-31')
INSERT INTO @Interval_List VALUES (28,  '2024',     '2024-01-01', '2024-12-31')

INSERT INTO @Interval_List VALUES (29,  'Previous_Month',  cast(dateadd(MM, datediff(MM, 0, getdate()) - 1, 0) as date), cast(DATEADD(MM, DATEDIFF(MM, -1, GETDATE())-1, -1) as date))
INSERT INTO @Interval_List VALUES (30,  'Current_Month',   cast(dateadd(MM, datediff(MM, 0, getdate()), 0) as date),     cast(DATEADD(MM, DATEDIFF(MM, -1, GETDATE()), -1) as date))
INSERT INTO @Interval_List VALUES (31,  'Previous_Week',   cast(dateadd(wk, datediff(wk, 0, getdate()) - 1, 0) as date), cast(DATEADD(wk, DATEDIFF(wk, 6, CURRENT_TIMESTAMP), 6) as date))
INSERT INTO @Interval_List VALUES (32,  'Current_Week',    cast(dateadd(wk, datediff(wk, 0, getdate()), 0) as date),     DATEADD(DAY, 8 - DATEPART(WEEKDAY, GETDATE()), CAST(GETDATE() AS DATE)))

DECLARE @StartDate AS DATE
DECLARE @EndDate AS DATE
DECLARE @CurrentDate AS DATE
DECLARE @index_first int
declare @index_last int
declare @interval VARCHAR(50)
declare @metric_table nvarchar(400)

SELECT @index_first = min(index_1),  @index_last = max(index_1) FROM @Interval_List

SET @CurrentDate = @StartDate
SET @metric_table = 'dbo.my_table'

WHILE (@index_first <= @index_last)
BEGIN

SELECT @StartDate = From_date,  @EndDate = To_date, @interval = Interval FROM @Interval_List where index_1 = @index_first

declare @query nvarchar(max);

set @query = 
'
SELECT 
Service_Group,
Portfolio,
etl_date,

sum(vulnerabilities) as vulnerabilities,
sum(security_hotspots) as security_hotspots,
sum(cast(uncovered_lines as float)) as uncovered_lines,
sum(cast(lines_to_cover as float)) as lines_to_cover,

sum(added_technical_debt) as added_technical_debt,
SUM(blocker_issues) as blocker_issues,
SUM(critical_issues) as critical_issues,
SUM(duplicated_blocks_on_new_code) as duplicated_blocks_on_new_code,
SUM(issues) as issues,
SUM(major_issues) as major_issues,

SUM(minor_issues) AS minor_issues,      
SUM(new_blocker_issues) AS new_blocker_issues,  
SUM(new_critical_issues) AS new_critical_issues,
SUM(new_issues) AS new_issues,          
SUM(new_major_issues) AS new_major_issues,  
SUM(new_minor_issues) AS new_minor_issues,  
SUM(technical_debt) AS technical_debt,      
SUM(unit_tests) AS unit_tests,          
SUM(wont_fix_issues) AS wont_fix_issues,    
SUM(bugs) AS bugs,              
SUM(code_smells) AS code_smells,        
SUM(duplicated_blocks) AS duplicated_blocks,    
SUM(duplicated_files) AS duplicated_files,  
SUM(new_bugs) AS new_bugs,          
SUM(new_code_smells) AS new_code_smells,    
SUM(new_security_hotspots) AS new_security_hotspots,
SUM(new_vulnerabilities) AS new_vulnerabilities,
SUM(open_issues) AS open_issues,


SUM(CASE WHEN ETL_DATE_SERVICE_STATUS_ID IN (12500, 15203)        OR Service_Group IS NULL THEN vulnerabilities ELSE NULL END) AS vulnerabilities_New, 
SUM(CASE WHEN ETL_DATE_SERVICE_STATUS_ID IN (12500, 15203, 10236) OR Service_Group IS NULL THEN vulnerabilities ELSE NULL END) AS vulnerabilities_OA,
SUM(vulnerabilities) as vulnerabilities,

SUM(CASE WHEN ETL_DATE_SERVICE_STATUS_ID IN (12500, 15203)        OR Service_Group IS NULL THEN security_hotspots ELSE NULL END) AS security_hotspots_New, 
SUM(CASE WHEN ETL_DATE_SERVICE_STATUS_ID IN (12500, 15203, 10236) OR Service_Group IS NULL THEN security_hotspots ELSE NULL END) AS security_hotspots_OA,
SUM(security_hotspots) as security_hotspots,

SUM(CASE WHEN ETL_DATE_SERVICE_STATUS_ID IN (12500, 15203)        OR Service_Group IS NULL THEN uncovered_lines ELSE NULL END) AS uncovered_lines_New, 
SUM(CASE WHEN ETL_DATE_SERVICE_STATUS_ID IN (12500, 15203, 10236) OR Service_Group IS NULL THEN uncovered_lines ELSE NULL END) AS uncovered_lines_OA,
SUM(uncovered_lines) as uncovered_lines,

SUM(CASE WHEN ETL_DATE_SERVICE_STATUS_ID IN (12500, 15203)        OR Service_Group IS NULL THEN lines_to_cover ELSE NULL END) AS lines_to_cover_New, 
SUM(CASE WHEN ETL_DATE_SERVICE_STATUS_ID IN (12500, 15203, 10236) OR Service_Group IS NULL THEN lines_to_cover ELSE NULL END) AS lines_to_cover_OA,
SUM(lines_to_cover) as lines_to_cover,

SUM(CASE WHEN ETL_DATE_SERVICE_STATUS_ID IN (12500, 15203)        OR Service_Group IS NULL THEN added_technical_debt ELSE NULL END) AS added_technical_debt_New, 
SUM(CASE WHEN ETL_DATE_SERVICE_STATUS_ID IN (12500, 15203, 10236) OR Service_Group IS NULL THEN added_technical_debt ELSE NULL END) AS added_technical_debt_OA,
SUM(added_technical_debt) as added_technical_debt,

SUM(CASE WHEN ETL_DATE_SERVICE_STATUS_ID IN (12500, 15203)        OR Service_Group IS NULL THEN blocker_issues ELSE NULL END) AS blocker_issues_New, 
SUM(CASE WHEN ETL_DATE_SERVICE_STATUS_ID IN (12500, 15203, 10236) OR Service_Group IS NULL THEN blocker_issues ELSE NULL END) AS blocker_issues_OA,
SUM(blocker_issues) as blocker_issues,

SUM(CASE WHEN ETL_DATE_SERVICE_STATUS_ID IN (12500, 15203)        OR Service_Group IS NULL THEN critical_issues ELSE NULL END) AS critical_issues_New, 
SUM(CASE WHEN ETL_DATE_SERVICE_STATUS_ID IN (12500, 15203, 10236) OR Service_Group IS NULL THEN critical_issues ELSE NULL END) AS critical_issues_OA,
SUM(critical_issues) as critical_issues,

SUM(CASE WHEN ETL_DATE_SERVICE_STATUS_ID IN (12500, 15203)        OR Service_Group IS NULL THEN duplicated_blocks_on_new_code ELSE NULL END) AS duplicated_blocks_on_new_code_New, 
SUM(CASE WHEN ETL_DATE_SERVICE_STATUS_ID IN (12500, 15203, 10236) OR Service_Group IS NULL THEN duplicated_blocks_on_new_code ELSE NULL END) AS duplicated_blocks_on_new_code_OA,
SUM(duplicated_blocks_on_new_code) as duplicated_blocks_on_new_code,

SUM(CASE WHEN ETL_DATE_SERVICE_STATUS_ID IN (12500, 15203)        OR Service_Group IS NULL THEN issues ELSE NULL END) AS issues_New, 
SUM(CASE WHEN ETL_DATE_SERVICE_STATUS_ID IN (12500, 15203, 10236) OR Service_Group IS NULL THEN issues ELSE NULL END) AS issues_OA,
SUM(issues) as issues,

SUM(CASE WHEN ETL_DATE_SERVICE_STATUS_ID IN (12500, 15203)        OR Service_Group IS NULL THEN major_issues ELSE NULL END) AS major_issues_New, 
SUM(CASE WHEN ETL_DATE_SERVICE_STATUS_ID IN (12500, 15203, 10236) OR Service_Group IS NULL THEN major_issues ELSE NULL END) AS major_issues_OA,
SUM(major_issues) as major_issues

FROM
(
SELECT 
Service_Group,
Portfolio,
etl_date,
ETL_DATE_SERVICE_STATUS_ID,
ETL_DATE_SERVICE_STATUS,

sum(vulnerabilities) as vulnerabilities,
sum(security_hotspots) as security_hotspots,
sum(cast(uncovered_lines as float)) as uncovered_lines,
sum(cast(lines_to_cover as float)) as lines_to_cover,

sum(added_technical_debt) as added_technical_debt,
SUM(blocker_issues) as blocker_issues,
SUM(critical_issues) as critical_issues,
SUM(duplicated_blocks_on_new_code) as duplicated_blocks_on_new_code,
SUM(issues) as issues,
SUM(major_issues) as major_issues,

SUM(minor_issues) AS minor_issues,      
SUM(new_blocker_issues) AS new_blocker_issues,  
SUM(new_critical_issues) AS new_critical_issues,
SUM(new_issues) AS new_issues,          
SUM(new_major_issues) AS new_major_issues,  
SUM(new_minor_issues) AS new_minor_issues,  
SUM(technical_debt) AS technical_debt,      
SUM(unit_tests) AS unit_tests,          
SUM(wont_fix_issues) AS wont_fix_issues,    
SUM(bugs) AS bugs,              
SUM(code_smells) AS code_smells,        
SUM(duplicated_blocks) AS duplicated_blocks,    
SUM(duplicated_files) AS duplicated_files,  
SUM(new_bugs) AS new_bugs,          
SUM(new_code_smells) AS new_code_smells,    
SUM(new_security_hotspots) AS new_security_hotspots,
SUM(new_vulnerabilities) AS new_vulnerabilities,
SUM(open_issues) AS open_issues         
'
  ' from '   @metric_table  
' where cast(FORMAT(etl_date, ''yyyyMMdd'') as varchar(30)) >= '   cast(FORMAT(@StartDate, 'yyyyMMdd') as varchar(30)) 
  ' AND cast(FORMAT(etl_date, ''yyyyMMdd'') as varchar(30)) <= '   cast(FORMAT(@EndDate, 'yyyyMMdd') as varchar(30))
 
'
GROUP BY Service_Group,Portfolio,etl_date,ETL_DATE_SERVICE_STATUS_ID,ETL_DATE_SERVICE_STATUS
) A 
GROUP BY Service_Group,Portfolio,etl_date, ETL_DATE_SERVICE_STATUS_ID ;
'

exec (@query)

SET @index_first = @index_first   1;
END
;

CodePudding user response：

The existing code uses naive string concatenation and is fraught with other issues. The following minor changes will protect you from SQL injection vectors in a couple of ways, and will also prevent the string from being truncated at 4,000 characters (which is what is happening now; you'll see this with PRINT LEN(@query); as shown in this fiddle). Removing all the SUM lines doesn't fix any syntax or eliminate any "nonsense" errors, it just makes the string < 4,000 characters.

IF OBJECT_ID(@metric_table) IS NULL
BEGIN
  RAISERROR('Bad object!', 11, 1);
  RETURN;
END

declare @query nvarchar(max) = N'
---- this N is important ------^
SELECT 
...
-- 500 lines here
...,
SUM(open_issues) AS open_issues from '   @metric_table 
    N' where etl_date >= @StartDate
  AND etl_date < DATEADD(DAY, 1, @EndDate)
GROUP BY ...;';

EXEC sys.sp_executesql @query, 
     N'@StartDate date, @EndDate date', 
     @StartDate, @EndDate;

With these minor changes, the string is no longer truncated to 4,000 characters.

For much more on dynamic SQL and SQL injection, see this list of resources.