Why to get error in SQL Server While Loop?

Question

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
;

mysql and sql server? It will either! But hey, that's a lot of code for anyone. — DhruvJoshi, Apr 22 '22 at 12:03
The easiest way to debug dynamic SQL is to `PRINT`/`SELECT` the statement first. Then you can debug that SQL first, and solve the problem before propagating the solution to your SQL that generates the dynamic statement. Often you'll find that the problems are quite simple, such as a typographical error that is difficult to stop in the literal strings, a missing whitespace/linebreak, or leading/trailing delimiters. Taking the time to get the non-dynamic statement working first is really important, as if that doesn't work the dynamic one will have no chance of working correctly. — Thom A, Apr 22 '22 at 12:06
**WARNING:** Your code is **dangerous**. It is wide open to SQL injection attacks. Always, *always, **always*** parametrise your code. [Why do we always prefer using parameters in SQL statements?](//stackoverflow.com/q/7505808) — Thom A, Apr 22 '22 at 12:07
It's also generally advised to not use syntax such as `EXEC (@SQL);`. Such statements cannot be parametrised, which promote bad habits that result in security flaws like SQL injection (which yours is). If you need to run a statement that is within a variable or literal string then use [`sys.sp_executesql`](https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-executesql-transact-sql). Then you can easily parametrise the statement if you need to. Also, you need to problem quote your dynamic objects, with `QUOTENAME`, as objects can't be parametrised. — Thom A, Apr 22 '22 at 12:08
@AlwaysLearning why add `'` characters when you could parametrise the values? Then there isn't an injection issue to start with. — Thom A, Apr 22 '22 at 12:13
Even just `PRINT LEN(@query);` will show the problem (the string is being truncated at 4,000 characters). Please review some of the advice [here](https://sqlblog.org/dynamic-sql). This also just seems really bad in general, in addition to the SQL injection nightmare, you're going to run this big dynamic aggregate query 32 times? I feel like there is a more efficient way to do this (but there's no way anyone is going to reverse engineer the wall of code shown here). — Aaron Bertrand, Apr 22 '22 at 12:15
I spent about half of my +40-year career doing complex nested dynamic SQL productions and what @Larnu says is dead on: the #1 rule to debugging or even just supporting dynamic SQL is to ***always*** print out the dynamic text before you execute it. I agree with everything else he says as well, except that for validation object names I highly recommend [this technique](https://stackoverflow.com/a/1246848/109122). I also use `QUOTENAMe(..)`, but that's primarily to catch passive injection. — RBarryYoung, Apr 22 '22 at 12:18
Yes, I completely agree that the object name(s) should also be validated, @RBarryYoung , I just ran out of space. and felt 3 comments was enough. :) At least `QUOTENAME` would cause an invalid object error for injection, rather than just allowing the query to go through. — Thom A, Apr 22 '22 at 12:20
Sorry, I said "passive injection" above, I should have said "*latent* injection". Latent SQL Injection is where the object names themselves have been pre-altered through other means to facilitate injection. (The "other method" is usually some commercial app or tool that allows users to make custom object with personal names within the app. And yes, the really do exist and are way more common than they should be). — RBarryYoung, Apr 22 '22 at 12:29

Aaron Bertrand · Accepted Answer · 2022-04-22T12:40:43.003

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.

Well, it works perfect after applying your changes. Literally you saved my life because I have been stuck with this error for a week. I appreciate your help Aaron, thank you very much :) — Cevat Erkibaş, Apr 22 '22 at 14:11

Why to get error in SQL Server While Loop?

1 Answers1