Dynamic query execution error I'm trying to execute the following dynamic query code:

  declare @sql nvarchar(max)
  set @sql = 'DECLARE @DatePartitionFunction nvarchar(max) = N''CREATE PARTITION FUNCTION DatePartitionFunction (datetime) 
                                             AS RANGE RIGHT FOR VALUES ('';
    DECLARE @incremental_date nvarchar(150);

    select @incremental_date = '+CONVERT(VARCHAR(24),GETDATE(),112)+'
    declare @counter int = 0;
    WHILE @counter &lt; 3
    BEGIN
        SET @DatePartitionFunction += '''' + CONVERT(VARCHAR(24),DATEADD(DD, @counter, @incremental_date),112) + '''' + N'', '';
        SET @counter += 1; 
    END

    SET @DatePartitionFunction += '''' +  CONVERT(VARCHAR(24),DATEADD(DD, @counter, @incremental_date),112)  + '''' + N'');'';
    print (@DatePartitionFunction)
    end;'

exec (@sql);


However, it returns an error stating incorrect syntax.

When I execute the same code outside the dynamic query context, it returns output. Stated below is the same code outside the dynamic query context

        DECLARE @DatePartitionFunction nvarchar(max) = N'CREATE PARTITION FUNCTION DatePartitionFunction (datetime) 
                                                         AS RANGE RIGHT FOR VALUES (';
        DECLARE @incremental_date nvarchar(150);

        select @incremental_date = CONVERT(VARCHAR(24),GETDATE(),112)
        declare @counter int = 0;
        WHILE @counter &lt; 3
        BEGIN
            SET @DatePartitionFunction += '''' + CONVERT(VARCHAR(24),DATEADD(DD, @counter, @incremental_date),112) + '''' + N', ';
            SET @counter += 1; 
            END

        SET @DatePartitionFunction += '''' +  CONVERT(VARCHAR(24),DATEADD(DD, @counter, @incremental_date),112)  + '''' + N');';
        print (@DatePartitionFunction);


It produces the desired output text as imparted below:

CREATE PARTITION FUNCTION DatePartitionFunction (datetime) 
 AS RANGE RIGHT FOR VALUES ('20150923', '20150924', '20150925', '20150926');


This is what i want the console to output in case of the aforementioned dynamic query, however I'm being met with errors.
Help!
The error you are receiving is because you have a trailing end; in your dynamic SQL version that is out of place (and doesn't exist in the non-dynamic version that works):

end;' --&gt; should be just '

Your dynamic SQL version is missing the required number of single quotes (so when you fix 1., you'll still have dates that look like integers).
This seems like a tidier approach, mostly because it avoids a lot of the single-quote-spaghetti, looping constructs, repetitive conversions, and changing the number of partitions requires only one change in the TOP() argument:

DECLARE @sql NVARCHAR(MAX) = N'CREATE PARTITION FUNCTION   
   DatePartitionFunction (datetime)
   AS RANGE RIGHT FOR VALUES (';  
;WITH c(rn) AS 
(
  SELECT TOP (4) CHAR(39) + 
    CONVERT(CHAR(8), DATEADD(DAY, ROW_NUMBER() OVER (ORDER BY name)-1, 
    GETDATE()), 112)  + CHAR(39)
  FROM sys.all_columns
)
SELECT @sql += STUFF((SELECT ',' + rn FROM c 
  ORDER BY rn FOR XML PATH, 
  TYPE).value(N'.[1]',N'varchar(max)'),1,1,'');
SET @sql += N');';

PRINT @sql;


Why you would then need to nest that in dynamic SQL, I'm not sure, but you could:

DECLARE @sql NVARCHAR(MAX) = N'
   DECLARE @pf NVARCHAR(MAX) = N''CREATE PARTITION FUNCTION   
   DatePartitionFunction (datetime)  
   AS RANGE RIGHT FOR VALUES ('';

;WITH c(rn) AS 
(
  SELECT TOP (4) CHAR(39) + 
    CONVERT(CHAR(8), DATEADD(DAY, ROW_NUMBER() OVER (ORDER BY name)-1, 
    GETDATE()), 112)  + CHAR(39)
  FROM sys.all_columns
)
SELECT @pf += STUFF((SELECT '','' + rn FROM c 
  ORDER BY rn FOR XML PATH, 
  TYPE).value(N''.[1]'',N''varchar(max)''),1,1,'''');
SET @pf += N'');'';

PRINT @pf;';

EXEC sys.sp_executesql @sql;

Please avoid lazy shorthand like DD. It takes an extra microsecond to type DAY, but the benefit is well worth it: it makes the code that much clearer and more self-documenting, and avoids fostering that shortcut for other dateparts, which can change the meaning and, therefore, the results (like y and w).