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!

Answer 1

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
;