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

I have a SSRSreport 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 inter开发者_如何转开发prets 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.

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.

Thanks for sharing your solution. I tried to implement the 2nd one but I was getting an error invoking the stored procedure. I had to tweak the syntax a little bit and this worked for me:

DECLARE @vSQL nvarchar(max) = N'
DECLARE @customerIdList Report.IntegerListTableType; 
'+@customerIdInserts+';
EXEC rpt_CustomerTransactionSummary
@startDate=@startDate,
@endDate=@endDate,
@accountType=@accountType,
@customerIds = @customerIdList';
exec sp_executesql 
  @vSQL,
  N'@startDate datetime, @endDate datetime, @accountType int',
  @startDate,
  @endDate',
  @accountType;

This syntax allows for NULL values too (for example, @accountType=NULL)