27,579
社区成员
发帖
与我相关
我的任务
分享
declare @school nvarchar(100)= N'%四川%' --如果有传入参数,%直接拼在参数中
DECLARE @sql nvarchar (MAX) --应该定义为 nvarchar(max),你的 100 长度不够
set @sql='
select * from hr_hi_person p left join hr_hi_edu edu
on edu.cPsn_Num=p.cPsn_Num
where 1=1 '
+(case when isnull(@school,'')!='' then ' and edu.vschool like @school ' else '' end)
--打印出来,观察实际的sql是否正确
PRINT @sql
--执行
EXEC sp_executesql @sql, N'@school nvarchar (100)',@school
declare @school nvarchar(100)=''
declare @sql nvarchar(max)
set @sql='select *
from hr_hi_person as p
left join hr_hi_edu edu
on edu.cPsn_Num=p.cPsn_Num
where
1=1
and p.cPsn_Num=''X001''
'
+
(case when @school>'' then ' edu.vschool=@school ' else '' end)
EXEC sp_executesql @sql,N'@school nvarchar(100)',@school
declare @school nvarchar(100)=''
declare @sql nvarchar(max)
set @sql='select *
from hr_hi_person as p
left join hr_hi_edu edu
on edu.cPsn_Num=p.cPsn_Num
where
1=1
and p.cPsn_Num='X001'
'
+
(case when @school>'' then ' edu.vschool=@school ' else '' end)
EXEC sp_executesql @sql,N'@school nvarchar(100)',@school
WHERE (@school = '' OR edu.vschool=@school)