Adding Quoted Datetimes from Paramaters in Dynamic SQL String for sp_executesql

Question

I am currently working on an SQL Server 2005, and trying to structure a dynamic query as follows:

DECLARE @GETDATE AS NVARCHAR(12);
DECLARE @GETDATE2 AS NVARCHAR(12);
SET @GETDATE = ...;
SET @GETDATE2 = ...;

SET @SQL =  
    'CREATE TABLE [dbo].[' + @TABLENAME + ']'
    +'(' 
    +'ShibNo' 'INT'
    +')' 
    +';'
    + CHAR(10) 
    +'INSERT INTO [dbo].[' + @TABLENAME + '] (ShibNo)'
    +'SELECT X.[ShibNo]'
    +'FROM'
    +'('
    +'SELECT'
    + 'I.[Shibno]'
    +',' + 'I.[ShibAzmnDate]'
    +',' + 'I.[ShibBeginTime]'
    +',' + 'I.[ShibEndTime]'
    +',' + 'I.[CarNum]'
    +',' + 'I.[DriverNo1]'
    +',' + 'I.[ShibKind]'
    +',' + 'I.[FStationID]'
    +',' + 'I.[LStationID]'
    +',' + 'I.[LineDetailRecordID]'
    +'FROM Inserted2 I'
    +'WHERE I.[ShibAzmnDate] BETWEEN ' + @GETDATE + ' AND ' + @GETDATE2 + 
    +'INTERCEPT'
    +'SELECT'
    + 'D.[Shibno]'
    +',' + 'D.[ShibAzmnDate]'
    +',' + 'D.[ShibBeginTime]'
    +',' + 'D.[ShibEndTime]'
    +',' + 'D.[CarNum]'
    +',' + 'D.[DriverNo1]'
    +',' + 'D.[ShibKind]'
    +',' + 'D.[FStationID]'
    +',' + 'D.[LStationID]'
    +',' + 'D.[LineDetailRecordID]'
    +'FROM Deleted2 D'
    +'WHERE D.[ShibAzmnDate] BETWEEN ' + @GETDATE + ' AND ' + @GETDATE2 + 
    +') AS X'
    +';'
;
EXECUTE sp_executesql @SQL
;

As you can see, there are parameters in the WHERE clauses of the query string which are meant to restrict the date range of the checks performed here. However, when the string is executed as a query using sp_executesql, the dates don't receive the proper apostrophe punctuation, which creates errors.

I have tried using replace and escaping the characters, but apparently do not know the proper way to do this. I would be very happy (and grateful!) to learn how to do this correctly.

The string returned if I check the build-up of the query is one of the following variations:

' WHERE D.[ShibAzmnDate] BETWEEN ''03/13/2016'' AND ''03/14/2016'' '

OR

' WHERE D.[ShibAzmnDate] BETWEEN 03/13/2016 AND 03/14/2016 '

OR

' WHERE D.[ShibAzmnDate] BETWEEN ''''03/13/2016'''' AND ''''03/14/2016'''' '

and so on...

Can someone please help me to understand how to properly structure this dynamic query string (and future dynamic query strings) to avoid this issue?

Many, many thanks in advance!

The complete query has to cross-reference multiple columns (those listed here) and check for inconsistencies. If any alteration is found in any of the listed columns, then the identity of the row changed (here named ShibNo) is inserted into the unique table for subsequent checks. — 3BK, Mar 13 '16 at 09:02
You can (and should) use variables with sp_executesql, there is no reason to concatenate your dates into the sql string, just use `BETWEEN @GETDATE AND @GETDATE2` — James Z, Mar 13 '16 at 09:06

score 1 · Accepted Answer · answered Mar 13 '16 at 09:06

Use parameter placeholders in the query, and then pass the values of the parameters to sp_executesql. See https://msdn.microsoft.com/en-us/library/ms188001.aspx for more details.

DECLARE @GETDATE AS NVARCHAR(12);
DECLARE @GETDATE2 AS NVARCHAR(12);
SET @GETDATE = ...;
SET @GETDATE2 = ...;

SET @SQL =  
    'CREATE TABLE [dbo].[' + @TABLENAME + ']'
    +'(' 
    +'ShibNo' 'INT'
    +')' 
    +';'
    + CHAR(10) 
    +'INSERT INTO [dbo].[' + @TABLENAME + '] (ShibNo)'
    +'SELECT X.[ShibNo]'
    +'FROM'
    +'('
    +'SELECT'
    + 'I.[Shibno]'

    . . .

    +'FROM Inserted2 I'
    +'WHERE I.[ShibAzmnDate] BETWEEN @StartDate AND @EndDate' 
    +'INTERCEPT'
    +'SELECT'
    + 'D.[Shibno]'

    . . .

    +'FROM Deleted2 D'
    +'WHERE D.[ShibAzmnDate] BETWEEN @StartDate AND @EndDate'
    +') AS X'
    +';'
;
EXECUTE sp_executesql @SQL, N'@StartDate DATE, @EndDate DATE',
    @StartDate = @GETDATE, @EndDate = @GETDATE2
;

Thank you for the answer. Believe it or not, I actually didn't know this, and it didn't come up during my searches for a solution, which is surprising... Thank you very much for your time. P.S. Would have voted this as an answer from JamesZ, who wrote it first, but he wrote it as a comment. — 3BK, Mar 13 '16 at 09:14

score 0 · Answer 2 · answered Mar 13 '16 at 09:01

0

Try it is working for me.

    DECLARE @DATE VARCHAR(250) = '2016-01-01', @VAR VARCHAR(MAX)

    SELECT @VAR = 'SELECT * FROM TABLE_A WHERE CREATE_DTE> '''+@DATE+''''

    SELECT @VAR

answered Mar 13 '16 at 09:01

Shakeer Mirza

5,054
2
18
41

Tried this. Yields ' WHERE D.[ShibAzmnDate] BETWEEN ''03/13/2016'' AND ''03/14/2016'' ' – 3BK Mar 13 '16 at 09:07

score 0 · Answer 3 · answered Mar 13 '16 at 09:09

1 MSSQL supports multiline string literals so you don't have to concat each line separately (don't quite understand why are you concatenating comma as a separate litera)

2 Since you are using sp_executesql and have variables of appropriate type you don't need them in sql text and don't have to convert them to varchar

SET @SQL =  
    cast('CREATE TABLE [dbo].[' as nvarchar(max)) + @TABLENAME + ']
    (
      ShibNo INT
    );

    INSERT INTO [dbo].[' + @TABLENAME + '] (ShibNo)
    SELECT X.[ShibNo]
    FROM
    (
      SELECT
         I.[Shibno]
        ,I.[ShibAzmnDate]
        ,I.[ShibBeginTime]
        ,I.[ShibEndTime]
        ,I.[CarNum]
        ,I.[DriverNo1]
        ,I.[ShibKind]
        ,I.[FStationID]
        ,I.[LStationID]
        ,I.[LineDetailRecordID]
      FROM Inserted2 I
      WHERE I.[ShibAzmnDate] BETWEEN @date1 AND @date2
      INTERCEPT
      SELECT
         D.[Shibno]
        ,D.[ShibAzmnDate]
        ,D.[ShibBeginTime]
        ,D.[ShibEndTime]
        ,D.[CarNum]
        ,D.[DriverNo1]
        ,D.[ShibKind]
        ,D.[FStationID]
        ,D.[LStationID]
        ,D.[LineDetailRecordID]
      FROM Deleted2 D
      WHERE D.[ShibAzmnDate] BETWEEN @date1 AND @date2
    ) AS X'

EXECUTE sp_executesql @SQL, N'@date1 datetime, @date2 datetime', @getdate, @getdate2;

Yes, cleaning up the string to not use concatenation is a good idea. Also `@SQL` does need to be `NVARCHAR`, as Ivan intimated. — gmm, Mar 13 '16 at 09:15
The current structure of the query is formatted "as is" from a previous scripter. I believe that the intention was to make the query clearer on the IDE screen, and to make it plainer to read in profiler, where a great deal of our work is often done. You are correct, of course, in both of your points, and I will be optimizing the structure of the query now that it is working properly. Thank you. — 3BK, Mar 13 '16 at 09:15

Adding Quoted Datetimes from Paramaters in Dynamic SQL String for sp_executesql

3 Answers3