复杂的存储过程，提示未声明变量
各位朋友，存储过程如下：
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+ &#