一段关于查询承运商信息的存储过程

仅供体会，无商业用途 (^_^)

---------------------------------------------------view-->vt_cysKh-----------------------------------------------------
if exists (select * from sysobjects where name = 'vt_cysKh')
 drop view vt_cysKh
go
create view vt_cysKh
as
 select n.cysmc,m.sum_price,m.khtype,m.khDate
 from 
 (
  select tk.cysbm,sum(tk.xmTimes*(cast(kk.money as int))) as sum_price, kk.khtype,tk.khDate 
  from T_kfxmpf tk  inner join kkxm kk on (tk.xmid =kk.id) 
  where state=3 group by tk.cysbm,kk.khtype,tk.khDate,tk.khDate
 ) as m, t_cysxx as n 
 where m.cysbm=n.cysbm
go

----------------------------------------procedure-->proc_fetchCysReport-------------------------------------------
if exists (select * from sysobjects where name = 'proc_fetchCysReport')
 drop procedure proc_fetchCysReport
go
create procedure proc_fetchCysReport
 @startDate varchar(16),
 @endDate  varchar(16)
as
declare cur cursor for select distinct khtype from (select n.cysmc,m.khtype from (select tk.cysbm, kk.khtype from T_kfxmpf tk  inner join kkxm kk on (tk.xmid =kk.id) where state=3) as m, t_cysxx as n where m.cysbm=n.cysbm) as s order by khtype asc
declare 
 @khtype varchar(20),
 @sum varchar(300),
 @case varchar(500),
 @sql nvarchar(1000)
set @sum = ''
set @case = ''
set @sql = ''
if @startDate = ''
   select @startDate = min(tk.khDate) from T_kfxmpf tk  inner join kkxm kk on (tk.xmid =kk.id) where state=3
if @endDate = ''
   select @endDate = max(tk.khDate) from T_kfxmpf tk  inner join kkxm kk on (tk.xmid =kk.id) where state=3
open cur
 fetch next from cur into @khtype
while @@fetch_status =0
begin
 set @sum = @sum + 'sum(' + @khtype + ') as '  + @khtype +','
 set @case = @case + '(case khtype when '''  + @khtype + ''' then isnull(sum_price,0) end) as ' + @khtype +','
 fetch next from cur into @khtype
end
close cur
deallocate cur

if right(@case,1) = ','
 set @case = stuff(@case, len(@case), 1, ' from vt_cysKh where khDate between ''' + @startDate + ''' and ''' + @endDate +'''')

if right(@sum,1) = ','
 set @sum = stuff(@sum,len(@sum),1,'')

set @sql = ' select cysmc, ' + @sum + ' from ( select cysmc, ' + @case + ') as b group by cysmc'
execute  sp_executesql @sql

select distinct b.khtype,a.sp 
from (select khtype,sum(sum_price) as sp from vt_cysKh where khDate between @startDate and @endDate  group by khtype) as a 
right outer join vt_cysKh as b on a.khtype = b.khtype
go

/*
select * from vt_cysKh
execute proc_fetchCysReport '2010-10-01','2011-02-14'
*/