Question

sql 存储过程报错。我想把它写成一个模糊搜索的存储过程。

create proc proc_SearchEmployeeNameFrom_trv_1
(
@Keyword varchar(100),
@OUID uniqueidentifier
)
as
select distinct EmployeeName, EmployeeNo, MobileNo, Email
from tbOUEmployee, tbOUOrganization
where (EmployeeName like '%'+@Keyword+'%'or EmployeeNo '%'+,@Keyword+'%')
and (tbOUEmployee.OUID = @OUID and tbOUEmployee.OUID = tbOUOrganization.OUID)

报错：消息 102，级别 15，状态 1，过程 proc_SearchEmployeeNameFrom_trv_1，第 9 行
'%' 附近有语法错误

Answer 1

declare @sql varchar(200),
SET @SQL='select distinct EmployeeName, EmployeeNo, MobileNo, Email
from tbOUEmployee, tbOUOrganization
where (EmployeeName like '%' '+@Keyword+''%'or EmployeeNo '%'+,@Keyword+'%') and (tbOUEmployee.OUID = @OUID and tbOUEmployee.OUID = tbOUOrganization.OUID) '
exec @sql
带变量的只能用扩展SQL

Answer 2

create proc proc_SearchEmployeeNameFrom_trv_1
(
@Keyword varchar(100),
@OUID uniqueidentifier
)
as
declare @mysql varchar(5000)
set @mysql=N'select distinct EmployeeName, EmployeeNo, MobileNo, Email
from tbOUEmployee, tbOUOrganization
where (EmployeeName like '%''+@Keyword+''%'or EmployeeNo '%''+@Keyword+''%')
and (tbOUEmployee.OUID ='+@OUID+' and tbOUEmployee.OUID = tbOUOrganization.OUID)'
exec (@sql)
只能用动态执行

Answer 3

EmployeeName like '%'+@Keyword+'%'or EmployeeNo '%'+,@Keyword+'%' 少了like吧？
EmployeeName like '%'+@Keyword+'%'or EmployeeNo like '%'+,@Keyword+'%'