费劲的写法

create procedure SearchUnconvent
@bm varchar(2),                                          --部门编号
@bh varchar(8),                                           --人员编号
@xm varchar(24),                                        --人员姓名  
@seldate datetime,                                       --查询日期
@mark varchar(4)                                        --查询条件
as
declare @p varchar(2)
select @p='%'
declare @m varchar(2)
select @m='*'
declare @tablename varchar(100)                ---@tablename得到查询表名
select @tablename='kqmx_'+cast(right(datename(year,@seldate),4) as varchar)+cast(datename(month,@seldate) as varchar)

if exists(select * from sysobjects where name=@tablename and type='u') ---判断数据库中是否存在该表
begin
 declare @sql varchar(3000)
 select @sql='select 部门=C.bms,姓名=C.xm,日期=C.rq,员工编号=C.bh,打卡时间=C.sj,'+
   '迟到=C.cd1,早退=C.zt,旷工=C.kg from '+
   '(select distinct bms=A.bms,xm=A.xm,rq=left(B.rq,10),bh=A.bh,sj=B.sj,'+
   'cd1=isnull(B.cd1,0) ,zt=isnull(B.zt,0) ,kg=isnull(B.kg,0),bm=A.bm  from V_ConnDocBmZw A '+
   'left join '+@tablename+' B on A.bh=B.bh '+
   'where B.lx0='+char(39)+@m+char(39)+' and B.rq='+char(39)+convert(varchar(10),@seldate)+char(39)+') C '+
   'where (cd1<>0 or zt<>0 or kg<>0 or sj='+char(39)+char(39)+')'         ----char(39)+char(39)  表示 ' ' 空 
 if (@bm<>'00')
 begin
 select @sql=@sql+' and C.bm='+char(39)+@bm+char(39)
 end
 
 if (@bh <>'')
 begin
 select @sql=@sql+' and C.bh like '+char(39)+@p+char(39)+'+'+char(39)+@bh+char(39)+'+'+char(39)+@p+char(39)
 end
 ------模糊查询

 if(@xm <>'')
 begin
 select @sql=@sql+' and C.xm like '+char(39)+@p+char(39)+'+'+char(39)+@xm+char(39)+'+'+char(39)+@p+char(39)
 end

 declare @selsql varchar(100)
 select @selsql='select 部门,姓名,日期,员工编号,打卡时间,'
 if(@mark='迟到')
 begin
 select @sql=@selsql+'迟到 from ('+@sql+') as Z where Z.迟到>0'
 end

 if(@mark='早退')
 begin
 select @sql=@selsql+'早退 from ('+@sql+') as Z where Z.早退>0'
 end

 if(@mark='旷工')
 begin
 select @sql=@selsql+'旷工 from ('+@sql+') as Z where Z.旷工>0'
 end
 
 select @sql=@sql+' order by Z.部门,Z.姓名,Z.员工编号'
 --print @sql
 exec (@sql)
end
else
return
go