费了大半天才完成的SQL语句,郁闷!!!!!!!!!!!!!!!!!!!!!!!!

create function TranData(@InTime varchar(8))
returns varchar(8)
as
begin
 declare @OutTime varchar(8)
 if (left(@InTime,1)<>'0')
 begin
 select @OutTime=left(@InTime,2)+right(@InTime,2)
 end
 else
 begin
  if(substring(@InTime,2,1)<>'0')
  begin
  select @OutTime=substring(@InTime,2,1)+right(@InTime,2) 
  end
  else
  begin
   if(substring(@InTime,4,1)<>'0')
   begin
   select @OutTime=right(@InTime,2)
   end
   else
   begin
   select @OutTime=right(@InTime,1)
   end
  end
 end
 return (@OutTime)
end



--查询打卡时间
create procedure SearchExecuteCard
@bm varchar(2),                                                                           ---部门编号
@xm varchar(24),                                                                         ---员工姓名  
@strdate datetime,                                                                        ----开始日期
@enddate datetime,                                                                      ----结束日期  
@begtime varchar(8),                                                                    ----开始时间 
@overtime varchar(8)                                                                   ----结束时间
as
declare @Btime varchar(8)
declare @Otime varchar(8)
select @Btime=dbo.TranData(@begtime)
select @Otime=dbo.TranData(@overtime)
declare @sql varchar(3000)
declare @S varchar(2)
select @s=':'
declare @k varchar(2)
select @k='0'
declare @j varchar(2)
select @j='00'
declare @p varchar(2)
select @p='%'
select @sql='select C.bms 部门,B.xm 姓名,B.bh 员工编号,A.rq 日期,'+
     'case len(A.sj)'+
     'when 2 then+'+char(39)+@j+char(39)+'+'+char(39)+@s+char(39)+'+right(A.sj,2)'+
     'when 3 then+'+char(39)+@k+char(39)+'+left(A.sj,1)+'+char(39)+@s+char(39)+'+right(A.sj,2)'+
     'when 4 then left(A.sj,2)+'+char(39)+@s+char(39)+'+right(A.sj,2)'+
     'end 打卡时间  from dbo.KqData A inner join dbo.Doc B on A.bh=B.bh'+
     ' inner join dbo.Bumen C on B.bm=C.bm where 1=1'
if(@Btime=@Otime)
begin
select @sql=@sql+' and A.sj='+char(39)+cast(@Btime as varchar(20))+char(39)
end

if (@strdate=@enddate)
begin
select @sql=@sql+' and convert(datetime,cast(A.rq as varchar(20)))='+char(39)+convert(varchar(20),@strdate)+char(39)
end

if(@bm<>'00')
begin
select @sql=@sql+' and C.bm='+char(39)+@bm+char(39)
end

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

if(@strdate<>@enddate)
begin
select @sql=@sql+' and convert(datetime,cast(A.rq as varchar(20))) between '
+char(39)+convert(varchar(20),@strdate)+char(39)+' and '
+char(39)+convert(varchar(20),@enddate)+char(39)
end

if(@Btime<>@Otime)
begin
select @sql=@sql+' and A.sj>='+char(39)+cast(@Btime as varchar(20))+char(39)+' and A.sj <='+char(39)+cast(@Otime as varchar(20))+char(39)
end

select @sql=@sql+' order by C.bms,B.xm,A.rq'
--print @sql
exec (@sql)
go