drop
procedure
zhcx
USE [ KJXM ]
GO
/**//* ***** Object: StoredProcedure [dbo].[myprocedure] Script Date: 03/18/2007 13:00:22 ***** */
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [ dbo ] . [ zhcx ]
(
@zjf_xx real , @zjf_sx real ,
@jhbk_xx real , @jhbk_sx real ,
@bmpt_xx real , @bmpt_sx real ,
@qyzc_xx real , @qyzc_sx real ,
@jhhb_xx real , @jhhb_sx real ,
@sjbk_xx real , @sjbk_sx real
)
as
begin
declare @sqlstr nvarchar ( 1000 )
set @sqlstr = N ' select 项目编号,项目类别,所属领域,项目年度,项目名称,完成日期,主持人,主持单位,
dbo.add_string(项目编号) as 参与单位,归口部门,总经费,计划拨款,部门配套,企业自筹,计划后补,
(select sum(实际拨款) from psjbk where 项目编号=pxmxx.项目编号 and 拨款日期!= '' 0 '' ) as 实际拨款
,计划类别 from [pxmxx] where 1=1 '
if ( @zjf_xx !=- 1 )
begin
set @sqlstr = @sqlstr + N ' and (总经费 between @zjf_xx and @zjf_sx) '
end
if ( @jhbk_xx !=- 1 )
begin
set @sqlstr = @sqlstr + N ' and (计划拨款 between @jhbk_xx and @jhbk_sx) '
end
if ( @bmpt_xx !=- 1 )
begin
set @sqlstr = @sqlstr + N ' and (部门配套 between @bmpt_xx and @bmpt_sx) '
end
if ( @qyzc_xx !=- 1 )
begin
set @sqlstr = @sqlstr + N ' and (企业自筹 between @qyzc_xx and @qyzc_sx) '
end
if ( @jhhb_xx !=- 1 )
begin
set @sqlstr = @sqlstr + N ' and (计划后补 between @jhhb_xx and @jhhb_sx) '
end
if ( @sjbk_xx !=- 1 )
begin
set @sqlstr = @sqlstr + N ' and ((select sum(实际拨款) from psjbk where psjbk.项目编号=pxmxx.项目编号 and 拨款日期!= '' 0 '' ) between @sjbk_xx and @sjbk_sx) '
end
-- select @sqlstr
-- /*
execute sp_executesql @sqlstr ,
N ' @zjf_xx real,@zjf_sx real,
@jhbk_xx real,@jhbk_sx real,
@bmpt_xx real,@bmpt_sx real,
@qyzc_xx real,@qyzc_sx real,
@jhhb_xx real,@jhhb_sx real,
@sjbk_xx real,@sjbk_sx real ' ,
@zjf_xx , @zjf_sx ,
@jhbk_xx , @jhbk_sx ,
@bmpt_xx , @bmpt_sx ,
@qyzc_xx , @qyzc_sx ,
@jhhb_xx , @jhhb_sx ,
@sjbk_xx , @sjbk_sx
-- */
end
USE [ KJXM ]
GO
/**//* ***** Object: StoredProcedure [dbo].[myprocedure] Script Date: 03/18/2007 13:00:22 ***** */
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [ dbo ] . [ zhcx ]
(
@zjf_xx real , @zjf_sx real ,
@jhbk_xx real , @jhbk_sx real ,
@bmpt_xx real , @bmpt_sx real ,
@qyzc_xx real , @qyzc_sx real ,
@jhhb_xx real , @jhhb_sx real ,
@sjbk_xx real , @sjbk_sx real
)
as
begin
declare @sqlstr nvarchar ( 1000 )
set @sqlstr = N ' select 项目编号,项目类别,所属领域,项目年度,项目名称,完成日期,主持人,主持单位,
dbo.add_string(项目编号) as 参与单位,归口部门,总经费,计划拨款,部门配套,企业自筹,计划后补,
(select sum(实际拨款) from psjbk where 项目编号=pxmxx.项目编号 and 拨款日期!= '' 0 '' ) as 实际拨款
,计划类别 from [pxmxx] where 1=1 '
if ( @zjf_xx !=- 1 )
begin
set @sqlstr = @sqlstr + N ' and (总经费 between @zjf_xx and @zjf_sx) '
end
if ( @jhbk_xx !=- 1 )
begin
set @sqlstr = @sqlstr + N ' and (计划拨款 between @jhbk_xx and @jhbk_sx) '
end
if ( @bmpt_xx !=- 1 )
begin
set @sqlstr = @sqlstr + N ' and (部门配套 between @bmpt_xx and @bmpt_sx) '
end
if ( @qyzc_xx !=- 1 )
begin
set @sqlstr = @sqlstr + N ' and (企业自筹 between @qyzc_xx and @qyzc_sx) '
end
if ( @jhhb_xx !=- 1 )
begin
set @sqlstr = @sqlstr + N ' and (计划后补 between @jhhb_xx and @jhhb_sx) '
end
if ( @sjbk_xx !=- 1 )
begin
set @sqlstr = @sqlstr + N ' and ((select sum(实际拨款) from psjbk where psjbk.项目编号=pxmxx.项目编号 and 拨款日期!= '' 0 '' ) between @sjbk_xx and @sjbk_sx) '
end
-- select @sqlstr
-- /*
execute sp_executesql @sqlstr ,
N ' @zjf_xx real,@zjf_sx real,
@jhbk_xx real,@jhbk_sx real,
@bmpt_xx real,@bmpt_sx real,
@qyzc_xx real,@qyzc_sx real,
@jhhb_xx real,@jhhb_sx real,
@sjbk_xx real,@sjbk_sx real ' ,
@zjf_xx , @zjf_sx ,
@jhbk_xx , @jhbk_sx ,
@bmpt_xx , @bmpt_sx ,
@qyzc_xx , @qyzc_sx ,
@jhhb_xx , @jhhb_sx ,
@sjbk_xx , @sjbk_sx
-- */
end
调用该存储过程的方法.
exec
myprocedure
@zjf_xx
=
0
,
@zjf_sx
=
100
,
@jhbk_xx
=
0
,
@jhbk_sx
=
100
,
@bmpt_xx
=
0
,
@bmpt_sx
=
100
,
@qyzc_xx
=
0
,
@qyzc_sx
=
100
,
@jhhb_xx
=
0
,
@jhhb_sx
=
100
,
@sjbk_xx
=-
1
,
@sjbk_sx
=
100
必须在 @sqlstr字符串后面 声名参数类型和传递参数值.