下面的#cltmp就是一个临时表: CREATE PROCEDURE ysyytj @frm smalldatetime,@to smalldatetime,@type tinyint=1,@cflb varchar(10)='%',@cflx varchar(10)='%', @ys varchar(10)='%',@ypid varchar(10)='%',@tjfw int AS declare @ysbm varchar(10),@frmstr varchar(10),@tostr varchar(10) set @frmstr=convert(varchar,@frm) set @tostr=convert(varchar,@to) if @type=1 begin create table #cltmp(ypsl float,fcypmc varchar(30),fcyw varchar(20),fcypgg varchar(20)) declare a cursor for select fcbm from bmys where fcbm<>'0' open a fetch next from a into @ysbm while( @@FETCH_STATUS<>-1) begin exec(' insert into #cltmp select * from bycf where fcysdm like @ysbm fetch next from a into @ysbm end --drop table [#cltmp] close a deallocate a select * From #cltmp end drop table [#cltmp] GO
将表建在本地,用完后删除
将表建在服务器的临时表空间中,用完后删除
将SQL查询结果放在TCliendDataSet中,可以对其进行各种操作.
......
且可动态加入数据
CREATE PROCEDURE ysyytj
@frm smalldatetime,@to smalldatetime,@type tinyint=1,@cflb varchar(10)='%',@cflx varchar(10)='%',
@ys varchar(10)='%',@ypid varchar(10)='%',@tjfw int
AS
declare @ysbm varchar(10),@frmstr varchar(10),@tostr varchar(10)
set @frmstr=convert(varchar,@frm)
set @tostr=convert(varchar,@to)
if @type=1
begin
create table #cltmp(ypsl float,fcypmc varchar(30),fcyw varchar(20),fcypgg varchar(20))
declare a cursor for select fcbm from bmys where fcbm<>'0'
open a
fetch next from a into @ysbm
while( @@FETCH_STATUS<>-1)
begin
exec('
insert into #cltmp
select * from bycf
where fcysdm like @ysbm
fetch next from a into @ysbm
end
--drop table [#cltmp]
close a
deallocate a
select * From #cltmp
end
drop table [#cltmp]
GO