0

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!

2
  1. 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 '
    
  2. 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).

  3. 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;
    
  4. 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).

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.

1

You just have an extra end.

Either add a begin ..end or remove end

enter image description here

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.