SQL Server: how does quoting work in creating columns inside for-loop dynamically?

Question

I think I am getting a quoting problem. I want to replace

  ,CAST(REPLACE([NET_VALUE_1M],',','.') as float)/SUM(CAST(REPLACE([NET_VALUE_1M],',','.') as float)) OVER (PARTITION BY [ID] ORDER BY [CMONTH] ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) as 'NET_VALUE_1M_PROP_2Mp'
  ,CAST(REPLACE([NET_VALUE_1M],',','.') as float)/SUM(CAST(REPLACE([NET_VALUE_1M],',','.') as float)) OVER (PARTITION BY [ID] ORDER BY [CMONTH] ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) as 'NET_VALUE_1M_PROP_3Mp'
  ,CAST(REPLACE([NET_VALUE_1M],',','.') as float)/SUM(CAST(REPLACE([NET_VALUE_1M],',','.') as float)) OVER (PARTITION BY [ID] ORDER BY [CMONTH] ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) as 'NET_VALUE_1M_PROP_4Mp'
  ,CAST(REPLACE([NET_VALUE_1M],',','.') as float)/SUM(CAST(REPLACE([NET_VALUE_1M],',','.') as float)) OVER (PARTITION BY [ID] ORDER BY [CMONTH] ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) as 'NET_VALUE_1M_PROP_5Mp'
  ,CAST(REPLACE([NET_VALUE_1M],',','.') as float)/SUM(CAST(REPLACE([NET_VALUE_1M],',','.') as float)) OVER (PARTITION BY [ID] ORDER BY [CMONTH] ROWS BETWEEN 5 PRECEDING AND CURRENT ROW) as 'NET_VALUE_1M_PROP_6Mp'
  ,CAST(REPLACE([NET_VALUE_1M],',','.') as float)/SUM(CAST(REPLACE([NET_VALUE_1M],',','.') as float)) OVER (PARTITION BY [ID] ORDER BY [CMONTH] ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) as 'NET_VALUE_1M_PROP_7Mp'
  ,CAST(REPLACE([NET_VALUE_1M],',','.') as float)/SUM(CAST(REPLACE([NET_VALUE_1M],',','.') as float)) OVER (PARTITION BY [ID] ORDER BY [CMONTH] ROWS BETWEEN 7 PRECEDING AND CURRENT ROW) as 'NET_VALUE_1M_PROP_8Mp'
  ,CAST(REPLACE([NET_VALUE_1M],',','.') as float)/SUM(CAST(REPLACE([NET_VALUE_1M],',','.') as float)) OVER (PARTITION BY [ID] ORDER BY [CMONTH] ROWS BETWEEN 8 PRECEDING AND CURRENT ROW) as 'NET_VALUE_1M_PROP_9Mp'
  ,CAST(REPLACE([NET_VALUE_1M],',','.') as float)/SUM(CAST(REPLACE([NET_VALUE_1M],',','.') as float)) OVER (PARTITION BY [ID] ORDER BY [CMONTH] ROWS BETWEEN 9 PRECEDING AND CURRENT ROW) as 'NET_VALUE_1M_PROP_10Mp'
  ,CAST(REPLACE([NET_VALUE_1M],',','.') as float)/SUM(CAST(REPLACE([NET_VALUE_1M],',','.') as float)) OVER (PARTITION BY [ID] ORDER BY [CMONTH] ROWS BETWEEN 10 PRECEDING AND CURRENT ROW) as 'NET_VALUE_1M_PROP_11Mp'
  ,CAST(REPLACE([NET_VALUE_1M],',','.') as float)/SUM(CAST(REPLACE([NET_VALUE_1M],',','.') as float)) OVER (PARTITION BY [ID] ORDER BY [CMONTH] ROWS BETWEEN 11 PRECEDING AND CURRENT ROW) as 'NET_VALUE_1M_PROP_12Mp'
  ,CAST(REPLACE([NET_VALUE_1M],',','.') as float)/SUM(CAST(REPLACE([NET_VALUE_1M],',','.') as float)) OVER (PARTITION BY [ID] ORDER BY [CMONTH] ROWS BETWEEN 12 PRECEDING AND CURRENT ROW) as 'NET_VALUE_1M_PROP_13Mp'

with a while loop

SELECT

      CAST(REPLACE([NET_VALUE_1M],',','.') as float)
      /
     SUM(CAST(REPLACE([NET_VALUE_1M],',','.') as float)) 
         OVER (PARTITION BY [ID] ORDER BY [CMONTH] 
            ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) as 'NET_VALUE_1M_PROP_2Mp'


      DECLARE @cnt INT =2;
      DECLARE @cnt_total INT =12;

      WHILE @cnt < @cnt_total 
      BEGIN
        DECLARE @SQL NVARCHAR(MAX);
        SET @SQL =',CAST(REPLACE([NET_VALUE_1M],',','.') as float)
             /
          SUM(CAST(REPLACE([NET_VALUE_1M],',','.') as float)) 
                 OVER (PARTITION BY [ID] ORDER BY [CMONTH] ROWS 
                   BETWEEN @cnt PRECEDING AND CURRENT ROW) 
                       as ['NET_VALUE_1M_PROP_']+@cnt+['Mp']'
      EXECUTE(@SQL)

        SET @cnt=@cnt+1;
      END;


  FROM [Channel_AGG]

causing an error:

Msg 102, Level 15, State 1, Line 13
Incorrect syntax near ','.

Msg 156, Level 15, State 1, Line 20
Incorrect syntax near the keyword 'FROM'.

How does the quoting work to declare the SQL command? I tried [' opening quote and '] for closing quote, as shown here How to create/add columns using a variable in a loop, but errors continuing. So to create the initial command inside a for-loop in SQL Server 2014?

@Jeremy correct, DELARE @SQL NVARCHAR(MAX), but the quoting issue persist. — hhh
– hhh, Commented Apr 21, 2017 at 11:57
Sorry but dynamic SQL does not work like that. You have to build the entire SQL statement, inside a string var, and then execute it. Can you explain why you want to move away from your first approach? Do the number of output columns continually change? — David Rushton
– David Rushton, Commented Apr 21, 2017 at 11:58

SqlZim · Accepted Answer · 2017-04-21 12:00:03Z

I am not sure what you're trying to do, or what you expect to happen when trying to execute that incomplete fragment of code, but your quoting problem can be solved by using two single quotes for each single quote inside a string. e.g.:

 ',CAST(REPLACE([NET_VALUE_1M],'','',''.'') as float)
             /
          SUM(CAST(REPLACE([NET_VALUE_1M],'','',''.'') as float)) 
                 OVER (PARTITION BY [ID] ORDER BY [CMONTH] ROWS 
                   BETWEEN '+convert(nvarchar(10),@cnt-1)+' PRECEDING AND CURRENT ROW) 
                       as [NET_VALUE_1M_PROP_'+convert(nvarchar(10),@cnt)+'Mp]'

You will also need to convert @cnt to a character type before concatenating.

You should declare your @sql outside of your loop, and probably be concatenating (e.g. @sql =+ ... or @sql = @sql + ...) instead of resetting the value inside the loop.

Instead of blinding trying to exec(@sql), you might want to see what you are creating with print @sql or select @sql instead.

Collectives™ on Stack Overflow

SQL Server: how does quoting work in creating columns inside for-loop dynamically?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related