复杂的存储过程，提示未声明变量
各位朋友，存储过程如下：
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[KaoQinQuery]
@a varchar(1), @sql_str varchar(50)
/*
这个存储过程是专门给查询用，可以按姓名或编号或卡号进行查询
*/
as
declare @str varchar(2000)
set @str=
'select *
from
(select emp_id, card_id, case when no_sign=0 then ' '否 ' ' else ' '是 ' ' end no_sign, emp_name,id_card,
isnull(position_name,null) position_name,
case when sex=0 then ' '男 ' ' else ' '女 ' ' end sex,
isnull(job.job_name,null) job_name,
convert(varchar(10),hire_date,21) hire_date,
isnull(depart.depart_name,null) depart_name,
isnull(status.status_name,null) status_name,
isnull(polity.polity_name,null) polity_name,
isnull(native.native_name,null) native_name,
isnull(Nation.Nation_name,null) Nation_name,
isnull(Education.Education,null) Education,
isnull(dorm.dorm_name,null) dorm_name,
birth_date,
case when marriage=0 then ' '未婚 ' ' else ' '已婚 ' ' end marriage, gd_date, phone_code,
post_code, email, address, gd_school, speciality
from employee
left outer join position on employee.position_id=position.position_id--找出职称
left outer join job on employee.job_id=job.job_id
left outer join depart on employee.depart_id=depart.depart_id
left outer join Status on employee.status_id=status.status_id
left outer join polity on employee.polity_id=polity.polity_id
left outer join native on employee.native_id=native.native_id
left outer join Nation on employee.Nation_id=Nation.Nation_id
left outer join Education on employee.edu_id=Education.edu_id
left outer join Dorm on employee.dorm_id=dorm.dorm_id) query
where (emp_id like @emp_id+ ' '% ' ') and (emp_name like @emp_name+ ' '% ' ') '
if @a= '1 '
set @str=@str+ 'where emp_id like ' ' '+@sql_str+ '% ' ' ';
if @a= '2 '
set @str=@str+ 'where emp_name like ' ' '+@sql_str+ '% ' ' ';
exec(@str)
Go
在调用的时候：exec [KaoQinQuery] @a= '1 ',@sql_str= '1 '
提示：消息 137，级别 15，状态 2，第 41 行
必须声明变量 '@emp_id '。
请问该怎样改这个存储过程，才能解决这个问题呢？请各位朋友赐教，谢谢！！！
[解决办法]
where (emp_id like @emp_id+ ' '% ' ') and (emp_name like @emp_name+ ' '% ' ') '
这里面的@emp_id是哪来的？
[解决办法]
where (emp_id like ' ' '+@emp_id+ '% ' ') and (emp_name like @emp_name+ ' '% ' ') '
[解决办法]
把这句去掉
where (emp_id like @emp_id+ ' '% ' ') and (emp_name like @emp_name+ ' '% ' ') '
[解决办法]
@emp_id 這個還沒有定義？？？，你就直接用了
[解决办法]
if @a= '1 '
set @str=@str+ ' where emp_id like ' ' '+@sql_str+ '% ' ' ';
if @a= '2 '
set @str=@str+ ' where emp_name like ' ' '+@sql_str+ '% ' ' ';
-- where 前加空格
