34,576
社区成员
发帖
与我相关
我的任务
分享
declare @dc char(2),@createtable varchar(8000),@insertsql varchar(8000),
@updatesql varchar(8000), @select varchar(1000),@bmmc varchar(100),@startrq char(10),@endrq char(10),
@xt char(7),@i integer,@yscz varchar(100),@sl decimal(18,0),@num varchar(2),@tmpxt char(7)
--{一、***********************************動態創建全局臨時表*******************************************
set ansi_warnings off
set nocount on
if object_id('tempdb..##temp_cpgzdc') is not null drop table ##temp_cpgzdc
if object_id('tempdb..##temp') is not null drop table ##temp
set @createtable='CREATE TABLE ##temp_cpgzdc (
[xt] [char] (7) NOT NULL ,
[pp_mc] [varchar] (50) NOT NULL ,
[xlb_mc] [varchar] (50) NOT NULL,
[xt_dj] [decimal](18, 2) default 0 ,
[zjhl] [decimal](18, 0) default 0 ,
[zxl] [decimal](18, 0) default 0 ,
[zjg] [decimal](18, 2) default 0 ,
[weekxl] [decimal](18, 0) NULL ,
[weekjg] [decimal](18, 2) default 0 '
declare cur_dc cursor for
select b_dm from bsc where b_dm>'01'
open cur_dc
fetch next from cur_dc into @dc
while @@fetch_status=0
begin
set @createtable=@createtable+ ',dr'+@dc+' decimal(10, 0) NULL ,dc'+@dc+' decimal(10, 0) NULL ,'
+'zxl'+@dc+' decimal(10, 0) NULL ,zjg'+@dc+' decimal(10, 0) NULL ,'
+'weekxl'+@dc+' decimal(10, 0) NULL ,weekjg'+@dc+' decimal(18, 2) NULL ,'
+'kc'+@dc+' decimal(10, 0) default 0 ,zt'+@dc+' decimal(10, 0) NULL,'
+'yhrq'+@dc+' datetime NULL ,sgrq'+@dc+' datetime NULL,'
+'ysczxl1'+@dc+' decimal(10, 0) default 0,ysczxl2'+@dc+' decimal(10, 0) default 0,'
+'ysczxl3'+ @dc+' decimal(10, 0) default 0,ysczxl4'+@dc+' decimal(10, 0) default 0,'
+'ysczxl5'+ @dc+' decimal(10, 0) default 0,ysczxl6'+@dc+' decimal(10, 0) default 0,'
+'ysczxl7'+ @dc+' decimal(10, 0) default 0,ysczxl8'+@dc+' decimal(10, 0) default 0,'
+'yscz1'+@dc+' varchar(60) NULL,yscz2'+@dc+' varchar(60) NULL,'
+'yscz3'+ @dc+' varchar(60) NULL,yscz4'+@dc+' varchar(60) NULL,'
+'yscz5'+ @dc+' varchar(60) NULL,yscz6'+@dc+' varchar(60) NULL,'
+'yscz7'+ @dc+' varchar(60) NULL,yscz8'+@dc+' varchar(60) NULL'
fetch next from cur_dc into @dc
end
close cur_dc
deallocate cur_dc
set @createtable=@createtable+') '
exec(@createtable)
--***********************************動態創建全局臨時表******************************************* 一}
------------------------二、插入符合條件的型體資料-----------------
set @insertsql=' insert into ##temp_cpgzdc (xt,pp_mc,xlb_mc,xt_dj) '
+' select distinct xt_dm,pp_mc,xlb_mct,xt_dj '
+' from xt,pp,xlb '
+' where xt_pp=pp_dm and xt_xlb=xlb_dm '
+' and substring(xt_ksly,1,4)='''+@year+''''
+' and xt_ks='''+@ks+''''
+@conditionPP
exec(@insertsql)
--exec(@createtable)
--***********************************動態創建全局臨時表******************************************* 一}
------------------------二、插入符合條件的型體資料-----------------
set @insertsql=' insert into ##temp_cpgzdc (xt,pp_mc,xlb_mc,xt_dj) '
+' select distinct xt_dm,pp_mc,xlb_mct,xt_dj '
+' from xt,pp,xlb '
+' where xt_pp=pp_dm and xt_xlb=xlb_dm '
+' and substring(xt_ksly,1,4)='''+@year+''''
+' and xt_ks='''+@ks+''''
+@conditionPP
exec (@createtable+@insertsql)
DECLARE @S VARCHAR(8000)
SET @S='SELECT * INTO # FROM TB SELECT * FROM #'
EXEC(@S)
那就别用全局临时表
用局部临时表,在exec()里应该是可以用的.如:
create table #(id int)
declare @sql varchar(200)
set @sql='insert into # select 1'
exec(@sql)
select * from #