存储过程中,如何动态创建临时表
并且这个临时表还要被其他存储过程调用
这个临时表的名字是程序随机创建的比如如何改写下面的sql语句为存储过程:
select ... into #t_1 from ... where ...select ...into #t_2 from .. select ... from #t_1 ,#t_2 ...
并且这个临时表还要被其他存储过程调用
这个临时表的名字是程序随机创建的比如如何改写下面的sql语句为存储过程:
select ... into #t_1 from ... where ...select ...into #t_2 from .. select ... from #t_1 ,#t_2 ...
as
begin
declare @sql varchar(8000)
select @sql=''
select @sql='select * into ##t_'+cast(@id as varchar(10))+' from tablename'
exec(@sql)
endexec up_createtmptable 12
as
begin
declare @sql varchar(8000)
select @sql=''
select @sql='select * into ##t_'+cast(@id as varchar(10))+' from sysobjects'
exec(@sql)
end
goexec up_createtmptable 12drop table ##t_12
drop proc up_createtmptable
@chPayUse_30 varchar(8000),
@chPayUse_31 varchar(8000),
@temp_3 varchar(30),
@temp_1 varchar(30),
@beginTime varchar(20),
@endTime varchar(20)
AS
begin
declare @sql varchar(8000)
select @sql=''
select @sql= 'select c.ChUnitCode, c.chUnitBrief, c.chunitqualitycode, ' +
' sum(case when (c.chZHType = ''20'' and a.chPayUse in(' + @chPayUse_30 +
' )) then a.decPayAmount else 0 end) as dsjzcddk, ' +
' sum(case when (c.chZHType = ''30'' and a.chPayUse in(' + @chPayUse_31 +
' )) then a.decPayAmount else 0 end) as tsjgcddk ' +
' into ' + @temp_3 +
' from ES_LSZ a, ES_YHZHB b, ' + @temp_1 + ' c ' +
' where a.chPayAccount = b.chBankAccount ' +
' and a.chStatus = ''8'' ' +
' and b.ChUnitCode = ''1000000'' ' +
' and a.chRecAccount = c.chBankAccount ' +
' and (convert(char(10),a.dDate,120) between' + @beginTime + ' and ' + @endTime + ') ' +
' group by c.chunitqualitycode, c.chUnitBrief, c.ChUnitCode ' +
' order by c.chunitqualitycode, c.ChUnitCode'exec(@sql)
end
GO
其中含temp的是临时表,在程序中随机得到,已经加了##。
执行到这个存储过程的时候,会报错:temp_1的临时变量无效;
09:12:01,572 INFO [STDOUT] execute catch java.sql.SQLException: [TXT-WY7UVCS
Z]对象名 '##temp_Dd14760210' 无效。而temp_1指的临时表是在另外一个,同这个存储过程像类似的存储过程中创建的。先于这个存储过程创建的。
-- Check the #WeekCharge table whether it existe
If Object_id('TempDB..#WeekCharge') Is Not NULL
INSERT INTO #WeekCharge(OldWeekChargeID,WeekChargeID)
VALUES(@WeekChargeID,@NewWeekChargeID)
ELSE
SELECT OldWeekChargeID=@WeekChargeID,WeekChargeID
INTO #WeekCharge
FROM WeekCharge
WHERE WeekChargeID=@NewWeekChargeID
怎么解决
exec ProcName1
但是要参数一样
对象名 '##temp_Jb24240701' 无效。其中Jb24240701是在程序中随机得来的。
并且这个临时表还要被其他存储过程调用
这个临时表的名字是程序随机创建的----------------------------------
sorry,昨天没有看仔细,在一个存储过程中动态创建的表,在本次存储过程中调用完毕后会自动的销毁,如果实在要用,就不要用临时表了,就用实际表就好了。就是要用完后drop