Dynamic query execution error

Question

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 < 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 < 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!

Aaron Bertrand · Accepted Answer · 2020-05-08 04:18:49Z

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;' --> 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).

db2 · Accepted Answer · 2015-09-23 15:33:55Z

1

Looks like you may not have properly escaped all quotes.

This:

SET @DatePartitionFunction += '''' + ...

...probably needs to become this:

SET @DatePartitionFunction += '''''''' + ...

And the same thing in a couple other places.

answered Sep 23, 2015 at 15:33

db2

9,7184 gold badges37 silver badges59 bronze badges

Add a comment |

Kin Shah · Accepted Answer · 2015-09-23 15:41:44Z

You just have an extra end.

Either add a begin ..end or remove end

  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 < 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);'

--print @sql
exec (@sql);

above outputs

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

Stack Exchange Network

Dynamic query execution error

3 Answers 3

Your Answer

Hot Network Questions

Dynamic query execution error

3 Answers 3

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions