While LOOP IN Dynamic QUERY

Question

BEGIN
DECLARE @sqlQuery VARCHAR(MAX)
SET @sqlQuery ='Select Style_Color, Style_Color_Desc as Description, 
 RPT, Weeks,  '

DECLARE @cnt INT = 1
DECLARE @TblCount NVARCHAR(1000) = (SELECT COUNT(ID) FROM table_Name)            

WHILE @cnt <= @TblCount
BEGIN
    SET @sqlQuery = @sqlQuery + 'max(CASE WHEN Cluster_ID = (Select STORE_ID from Table_Name where id= '+@cnt+')
     then CAST(c.APS_Dev as decimal(10,2)) end) as ''APS Dev'' '


     SET @cnt = @cnt + 1
END

SET @sqlQuery = @sqlQuery+'                                          
 Minimum as Pres_Min  From table_Name '

PRINT(@SQLQuery)
END

I am getting error:

Conversion failed when converting the varchar value 'Select Style_Color, Style_Color_Desc as Description,RPT, Weeks, Select STORE_ID from table_name where id= to data type int.

Some times when I do few changes getting this error

Cannot perform an aggregate function on an expression containing an aggregate or a subquery

Could anyone help me how to declare while loop in dynamic query?

You're trying to add `@cnt` (an `INT`) to a string. If you want that number included in the string then you need to first `CAST` it — Tom H, Apr 03 '18 at 19:12
This is a mess. What are you really trying to do here? As posted you will have a bunch of columns with the same name. This is a classic xy problem. http://xyproblem.info/ — Sean Lange, Apr 03 '18 at 19:20
I very strongly doubt that you need a while loop here to build dynamic sql. This has the look of something that would be really easy to do as a set based query. Please see this article if you really want some help. http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/ — Sean Lange, Apr 03 '18 at 19:24
Possible duplicate of [Dynamic SQL error converting nvarchar to int](https://stackoverflow.com/questions/6034441/dynamic-sql-error-converting-nvarchar-to-int) — Tab Alleman, Apr 03 '18 at 19:29
Yes, step way, WAY back from what you've done so far, and tell us what you're trying to do. You have almost certainly started out in completely the wrong direction. — Tab Alleman, Apr 03 '18 at 19:31

score 1 · Accepted Answer · answered Apr 04 '18 at 03:51

In your case you can use two variants:

1) to use CAST or CONVERT function

-- CAST
SET @sqlQuery += 'max(CASE WHEN Cluster_ID = (Select STORE_ID from Table_Name where id= '+CAST(@cnt AS varchar(5))+')
then CAST(c.APS_Dev as decimal(10,2)) end) as ''APS Dev'' '

-- CONVERT
SET @sqlQuery += 'max(CASE WHEN Cluster_ID = (Select STORE_ID from Table_Name where id= '+CONVERT(varchar(5),@cnt)+')
then CAST(c.APS_Dev as decimal(10,2)) end) as ''APS Dev'' '

2) to use CONCAT function for strings concatenation

SET @sqlQuery += CONCAT('max(CASE WHEN Cluster_ID = (Select STORE_ID from Table_Name where id= ',@cnt,')
then CAST(c.APS_Dev as decimal(10,2)) end) as ''APS Dev'' ')

While LOOP IN Dynamic QUERY

1 Answers1