动态SQL拼接日期参数报date与int类型不兼容问题解决

三个核心问题导致报错：

• 拼接日期条件时，传入的日期值没有加单引号包裹，SQL引擎会把2022-06-01这类字符串识别为算术减法表达式，计算后得到整数结果，和date类型字段比较时就会抛出Operand type clash: date is incompatible with int的类型冲突错误。
• 若直接把日期变量转为date类型再做字符串拼接，date类型不支持+字符串拼接运算符，就会触发date类型与+运算符不兼容的错误。
• 额外隐患：原动态SQL里，子查询部分的库名还是硬编码的[AbellioWMTData_April_21]，没有替换为动态传入的@DatabaseName参数，后续切换数据库时查询会出错。

方案1：修正字符串拼接逻辑（兼容OPENQUERY写法，适配SSRS跨服务器查询场景） #

拼接日期时手动补充转义后的单引号，同时替换所有硬编码的库名，用QUOTENAME()包裹服务器、库名标识符避免特殊字符语法错误，最后拼接出完整的OPENQUERY语句执行：

-- 定义入参（SSRS中可将这几个参数绑定为报表入参）
declare @ServerName nvarchar(100) = '目标链接服务器名'
declare @DatabaseName nvarchar(100) = '目标数据库名'
declare @startDate varchar(20) = '2022-06-01'
declare @endDate varchar(20) = '2022-06-29'
declare @sql nvarchar(max)
declare @OPENQUERY nvarchar(max)

-- 拼接内层查询逻辑，日期值两侧用两个单引号转义，最终生成的SQL会保留单引号包裹日期
set @sql = '
Select distinct instanceId as [JourneyID], 
  CAST((select case when ISDATE(questionComment) = 1 THEN CAST(questionComment as date) END 
        from ' + QUOTENAME(@DatabaseName) + '.[dbo].[AB_SurveyQandA] 
        where protoQuestionId = 170346 and instanceId = qanda.instanceId ) as varchar) as [AuditDate]
  ,CAST((select case when ISDATE(UpdateDate) = 1 THEN CAST(UpdateDate as date) END 
        from ' + QUOTENAME(@DatabaseName) + '.[dbo].[AB_SurveyQandA] 
        where protoQuestionId = 176537 and instanceId = qanda.instanceId) as varchar) as [UpdatedDate]
from ' + QUOTENAME(@DatabaseName) + '.[dbo].[AB_SurveyQandA] qanda 
where 
(select case when ISDATE(questionComment) = 1 THEN CAST(questionComment as date) END 
 from ' + QUOTENAME(@DatabaseName) + '.[dbo].[AB_SurveyQandA] 
 where protoQuestionId = 170346 and instanceId = qanda.instanceId ) >= ''' + @startDate + '''
and (select case when ISDATE(questionComment) = 1 THEN CAST(questionComment as date) END 
     from ' + QUOTENAME(@DatabaseName) + '.[dbo].[AB_SurveyQandA] 
     where protoQuestionId = 170346 and instanceId = qanda.instanceId ) <= ''' + @endDate + '''
and UpdateDate is not null
'

-- 拼接OPENQUERY外层，将内层SQL的单引号二次转义适配OPENQUERY的字符串参数要求
set @OPENQUERY = 'Select [JourneyID], [AuditDate], [UpdatedDate] from OPENQUERY(' + QUOTENAME(@ServerName) + ',''' + REPLACE(@sql, '''', '''''') + ''')'

-- 执行查询
exec sp_executesql @OPENQUERY

方案2：参数化动态执行（本地跨库场景推荐，安全性更高） #

如果不需要走链接服务器OPENQUERY，直接在本地执行跨库查询，可利用sp_executesql的参数传值能力，完全不需要拼接日期字符串，彻底避免单引号转义和类型冲突问题：

declare @DatabaseName nvarchar(100) = '目标数据库名'
declare @startDate date = '2022-06-01'
declare @endDate date = '2022-06-29'
declare @sql nvarchar(max)

set @sql = '
Select distinct instanceId as [JourneyID], 
  CAST((select case when ISDATE(questionComment) = 1 THEN CAST(questionComment as date) END 
        from ' + QUOTENAME(@DatabaseName) + '.[dbo].[AB_SurveyQandA] 
        where protoQuestionId = 170346 and instanceId = qanda.instanceId ) as varchar) as [AuditDate]
  ,CAST((select case when ISDATE(UpdateDate) = 1 THEN CAST(UpdateDate as date) END 
        from ' + QUOTENAME(@DatabaseName) + '.[dbo].[AB_SurveyQandA] 
        where protoQuestionId = 176537 and instanceId = qanda.instanceId) as varchar) as [UpdatedDate]
from ' + QUOTENAME(@DatabaseName) + '.[dbo].[AB_SurveyQandA] qanda 
where 
(select case when ISDATE(questionComment) = 1 THEN CAST(questionComment as date) END 
 from ' + QUOTENAME(@DatabaseName) + '.[dbo].[AB_SurveyQandA] 
 where protoQuestionId = 170346 and instanceId = qanda.instanceId ) >= @startDate
and (select case when ISDATE(questionComment) = 1 THEN CAST(questionComment as date) END 
     from ' + QUOTENAME(@DatabaseName) + '.[dbo].[AB_SurveyQandA] 
     where protoQuestionId = 170346 and instanceId = qanda.instanceId ) <= @endDate
and UpdateDate is not null
'

-- 直接传入日期参数执行，无需做字符串拼接
exec sp_executesql @sql, N'@startDate date, @endDate date', @startDate, @endDate

注意：SSRS报表场景下使用OPENQUERY写法时，不要直接将用户输入的内容无转义拼接进SQL，避免SQL注入风险。

内容的提问来源于stack exchange，提问作者Richard Wimbridge