一个动态的存储过程

------------------------------------
--用途：作废票据统计汇总
--项目名称：
--说明：MXF
--时间：2007-3-26 10:30:54
------------------------------------

CREATE PROCEDURE dbo.Stat_BillFlaseUse
@orgcode varchar(9),
@time1 varchar(10),
@time2 varchar(10),
@billtypecode varchar(9)

AS

CREATE TABLE #TempTable2
(
   id int IDENTITY (1, 1) NOT NULL ,

   stime varchar(10) ,
   scity1 varchar(20) ,
   scity2 varchar(20) ,
   BillTypeCode varchar(9) ,
   BillFare varchar(20) ,
   startnumber varchar(9) ,
   endnumber    varchar(9) ,
   startEnd          varchar(20) ,
   orgcode          varchar(9)

)

declare @strsql nvarchar(4000)
declare @strWhere nvarchar(500)

set @strsql=' insert #TempTable2 select * from v_BillFalseUseStat '
set @strWhere=''

if(len(@orgcode)>=1 or len(@time1)>=1 or len(@time2)>=1 or len(@billtypecode)>=1)
begin
set @strWhere=' where '
end

if(len(@orgcode)>=1)--表示有值不为空
begin
if(len(LTRIM (RTRIM (@strWhere)))>5) begin set @strWhere=@strWhere+' and Orgcode='''+@orgcode+''' ' end
else begin set @strWhere=@strWhere+' Orgcode='''+@orgcode+''' ' end
end

if(len(@time1)>1)--表示有值不为空
begin
if(len(LTRIM (RTRIM (@strWhere)))>5) begin set @strWhere=@strWhere+' and BlankBillApplyTime>='''+@time1+'''' end
else begin set @strWhere=@strWhere+' BlankBillApplyTime>='''+@time1+'''' end

end

if(len(@time2)>1)--表示有值不为空
begin
if(len(LTRIM (RTRIM (@strWhere)))>5) begin set @strWhere=@strWhere+' and BlankBillApplyTime<='''+@time2 +''' ' end
else begin set @strWhere=@strWhere+' BlankBillApplyTime<='''+@time2+'''' end
end

if(len(@billtypecode)>=1)--表示有值不为空
begin
if(len(LTRIM (RTRIM (@strWhere)))>5) begin set @strWhere=@strWhere+' and billtypecode='''+@billtypecode+''' ' end
else begin set @strWhere=@strWhere+' billtypecode='''+@billtypecode+''' ' end

end

set @strsql=@strsql+@strWhere

exec sp_executesql @strsql

insert #TempTable2 SELECT '','', '', '', '', '', '总计(份)', (cast( sum( Cast(replace (startEnd,'份','') as int)) as varchar(20))+'份') as startEnd,'' FROM #TempTable2

select * from #TempTable2 order by id asc
GO