exec sp_executesql error 'Incorrect syntax near 1' when using datetime parameter

Question

I have a Ssrs report which sends the following text to the database :

EXEC ( 'DECLARE @TeamIds as TeamIdTableType ' + @Teams + ' EXEC rpt.DWTypeOfSicknessByCategoryReport @TeamIds , ' + @DateFrom + ', ' + @DateTo + ', ' + @InputRankGroups + ', ' + @SubCategories )

When I view this in profiler it interprets this as :

exec sp_executesql N'EXEC ( ''DECLARE @TeamIds as TeamIdTableType '' + @Teams + '' EXEC rpt.DWTypeOfSicknessByCategoryAndEmployeeDetailsReport @TeamIds, '' + @DateFrom + '', '' + @DateTo + '', '' + @InputRankGroups + '', '' + @SubCategories )',N'@Teams nvarchar(34),@DateFrom datetime,@DateTo datetime,@InputRankGroups varchar(1),@SubCategories bit',@Teams=N'INSERT INTO @TeamIds VALUES (5); ',@DateFrom='2010-02-01 00:00:00',@DateTo='2010-04-30 00:00:00',@InputRankGroups=N'1',@SubCategories=1

When this sql runs it errors, on the dates. I have tried changing the format of the date but it does not help. If I remove the dates it works fine.

Any help would be appreciated.

Answer 1

I think I know what's going on here. If the SQL that is executed is

EXEC ( 'DECLARE @TeamIds as TeamIdTableType ' + @Teams
    + ' EXEC rpt.DWTypeOfSicknessByCategoryReport @TeamIds , '
    + @DateFrom + ', '
    + @DateTo + ', '
    + @InputRankGroups + ', '
    + @SubCategories )

... SQL Server will do the string concatenation first and then call EXEC on the concatenated string. With the values of the parameters given, this means the concatenated string will be something like the following (with whitespace adjusted for readability):

DECLARE @TeamIds as TeamIdTableType
INSERT INTO @TeamIds VALUES (5);
EXEC rpt.DWTypeOfSicknessByCategoryReport @TeamIds ,
    2010-02-01 00:00:00, 2010-04-30 00:00:00, 1, 1

The problem is that EXEC is not a parameterized query, so it has to interpret the datetime parameters as raw strings, but as raw unquoted strings they are not proper syntax. You can fix this one of two ways.

First, the easy way: quote the datetime strings.

EXEC ( 'DECLARE @TeamIds as TeamIdTableType ' + @Teams
    + ' EXEC rpt.DWTypeOfSicknessByCategoryReport @TeamIds , '
    + '''' + @DateFrom + ''', '
    + '''' + @DateTo + ''', '
    + @InputRankGroups + ', '
    + @SubCategories )

This gives you something with valid syntax:

DECLARE @TeamIds as TeamIdTableType
INSERT INTO @TeamIds VALUES (5);
EXEC rpt.DWTypeOfSicknessByCategoryReport @TeamIds ,
    '2010-02-01 00:00:00', '2010-04-30 00:00:00', 1, 1

Second, the almost-as-easy but more correct way: use a parameterized query.

DECLARE @sql varchar(max)
SET @sql = 'DECLARE @TeamIds AS TeamIdTableType ' + @Teams
    + ' EXEC rpt.DWTypeOfSicknessByCategoryReport @TeamIds , @DateFrom, @DateTo, @InputRankGroups, @SubCategories'
EXEC sp_executesql @sql, N'@DateFrom datetime,@DateTo datetime,@InputRankGroups varchar(1),@SubCategories bit',
    @DateFrom, @DateTo, @InputRankGroups, @SubCategories

This way you don't have to worry about how to escape particular value types, although it is slightly more verbose. Note that you can't completely parameterize it due to passing raw SQL as a parameter in @Teams. This may not be something you can control -- I'm not that familiar with SSRS.