复杂的存储过程，提示未声明变量

[aaee_1980]

各位朋友，存储过程如下：
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'。

请问该怎样改这个存储过程，才能解决这个问题呢？请各位朋友赐教，谢谢！！！

[aaee_1980]

各位朋友，谢谢您们的热心回复，我将那句去掉了，改成这个样子
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'

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)


调用时这样写：exec KaoQinQuery @a='1',@sql_str='1'

提示错误：消息 170，级别 15，状态 1，第 39 行
第 39 行: 'emp_id' 附近有语法错误。


这倒底是啥原因，希望各位朋友再次赐教，谢谢！！！

[Limpire]

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 前加空格

[aaee_1980]

谢谢楼上的各位朋友的热心回复，谢谢！
结贴！

[chuifengde]

where (emp_id like @emp_id+''%'') and (emp_name like @emp_name+''%'')'

这里面的@emp_id是哪来的？

[Limpire]

where (emp_id like '''+@emp_id+'%'') and (emp_name like @emp_name+''%'')'

[kelph]

把这句去掉
where (emp_id like @emp_id+''%'') and (emp_name like @emp_name+''%'')'

[Limpire]

where (emp_id like @emp_id+''%'') and (emp_name like @emp_name+''%'')'
---------------------------
@emp_id和@emp_name都没有定义，干啥用的

[fa_ge]

@emp_id 這個還沒有定義？？？，你就直接用了