我正在执行动态SQL查询.其中参数是动态传递的.我首先写了类似下面的代码.
DECLARE @Id nvarchar(max);
DECLARE @query nvarchar(max);
SET @Id ='RMH_108'
SET @query ='Select *
FROM [A06].[Syn_RMDemand]
WHERE RMHierarchyId =' + @Id
PRINT @query
EXEC(@query)
然后我写了这个.
DECLARE @Id nvarchar(max);
DECLARE @query nvarchar(max);
SET @Id ='RMH_108'
SET @query ='Select *
FROM [A06].[Syn_RMDemand]
WHERE RMHierarchyId = '''+@Id+''''
PRINT @query
EXEC(@query)
此时间代码已成功执行.由于参数可以为空,我需要将其转换为null.我修改了代码并写了这个
DECLARE @Id nvarchar(max);
DECLARE @query nvarchar(max);
SET @Id = ''
SET @Id = NULLIF(@Id,'')
-- COMMENTED SET @Id ='RMH_108'
SET @query ='Select * FROM [A06].[Syn_RMDemand]
WHERE RMHierarchyId = '''+@Id+''''
PRINT @query
EXEC(@query)
解决方法
无需将其转换为NULL,只需检查您的值
DECLARE @Id nvarchar(max);
DECLARE @query nvarchar(max);
SET @Id = ''
-- COMMENTED SET @Id ='RMH_108'
SET @query ='Select * FROM [A06].[Syn_RMDemand]
WHERE RMHierarchyId = '''+@Id+''''
PRINT @query
EXEC(@query)
