要写个存储过程,因为字段是动态的,里面除了嵌套其他存储过程,还要执行用字符串组合的一段SQL语句
比如今天有50个字段,明天说不定就是52个字段,后天就是57个字段,这些字段都记录在MstGridCol表里面,而后面的表链接都存入GridSQLStatement里面Declare @SQLStatement VarChar(Max)
/*这个初始化一个物品信息表,大概5-6秒左右*/
Exec SP_Initialize '##ItemInfo',' and SellMonth in (Select Distinct Top 2 Month(SellDate) from ItemSellInfo Order By SellDate Desc)'
/*这里从MstGridCol表中取字段并组合,比如组合后是
Select 'Sell' As ProcessClass,b.ItemClass,a.ItemName,c.PayState
*/
Set @SQLStatement='Select 'Sell' As ProcessClass'
Select @SQLStatement=@SQLStatement+','+DataField from MstGridCol where Formname='ItemSellInfo' And GridName='Detail2'
/*这里从GridSQLStatement 取form之后的表组合字符串,比如组合后是
from ItemCSC a inner join MstItemClass on a.ItemClass=b.ItemClass Left join ItemPayInfo c on a.PayMonth=c.PayMonth where a.ItemType='Online'
*/
IF Exists (select Name from Tempdb..SysObjects where Name='##Reuslt') Drop Table ##GarProdInqCSC
Select @SQLStatement=@SQLStatement+' Into ##Reuslt '+SQLStatement from GridSQLStatement where Formname='ItemSellInfo' And GridName='Detail2'
/*组合后执行,由于表数量过多,数据量大,所以组合后执行时间大概为60秒以上*/
Exec (@SQLStatement)IF Exists (select Name from SysObjects where Name='ItemCSC') Drop Table ItemCSC
Exec ('Select * Into ItemCSC from ##Reuslt')
现在问题就来了,SQL实行过程中提示找不到##Reuslt,我猜测是组合字符串执行那段运行时间中,SQL没等这段执行完
Exec (@SQLStatement)
就执行了
Exec ('Select * Into ItemCSC from ##Reuslt')
我不知道怎么让SQL能等做完上一步的EXEC工作后才执行下一步的EXEC
我在中间加了个Exec (@SQLStatement)
WAITFOR DELAY '00:02:00'
IF Exists (select Name from SysObjects where Name='ItemCSC') Drop Table ItemCSC
Exec ('Select * Into ItemCSC from ##Reuslt')
SQLEXEC
比如今天有50个字段,明天说不定就是52个字段,后天就是57个字段,这些字段都记录在MstGridCol表里面,而后面的表链接都存入GridSQLStatement里面Declare @SQLStatement VarChar(Max)
/*这个初始化一个物品信息表,大概5-6秒左右*/
Exec SP_Initialize '##ItemInfo',' and SellMonth in (Select Distinct Top 2 Month(SellDate) from ItemSellInfo Order By SellDate Desc)'
/*这里从MstGridCol表中取字段并组合,比如组合后是
Select 'Sell' As ProcessClass,b.ItemClass,a.ItemName,c.PayState
*/
Set @SQLStatement='Select 'Sell' As ProcessClass'
Select @SQLStatement=@SQLStatement+','+DataField from MstGridCol where Formname='ItemSellInfo' And GridName='Detail2'
/*这里从GridSQLStatement 取form之后的表组合字符串,比如组合后是
from ItemCSC a inner join MstItemClass on a.ItemClass=b.ItemClass Left join ItemPayInfo c on a.PayMonth=c.PayMonth where a.ItemType='Online'
*/
IF Exists (select Name from Tempdb..SysObjects where Name='##Reuslt') Drop Table ##GarProdInqCSC
Select @SQLStatement=@SQLStatement+' Into ##Reuslt '+SQLStatement from GridSQLStatement where Formname='ItemSellInfo' And GridName='Detail2'
/*组合后执行,由于表数量过多,数据量大,所以组合后执行时间大概为60秒以上*/
Exec (@SQLStatement)IF Exists (select Name from SysObjects where Name='ItemCSC') Drop Table ItemCSC
Exec ('Select * Into ItemCSC from ##Reuslt')
现在问题就来了,SQL实行过程中提示找不到##Reuslt,我猜测是组合字符串执行那段运行时间中,SQL没等这段执行完
Exec (@SQLStatement)
就执行了
Exec ('Select * Into ItemCSC from ##Reuslt')
我不知道怎么让SQL能等做完上一步的EXEC工作后才执行下一步的EXEC
我在中间加了个Exec (@SQLStatement)
WAITFOR DELAY '00:02:00'
IF Exists (select Name from SysObjects where Name='ItemCSC') Drop Table ItemCSC
Exec ('Select * Into ItemCSC from ##Reuslt')
SQLEXEC
而是动态拼接出create table语句和insert... select语句
我在怀疑我的思路是不是一开始就错了,请教下大家有没有更好的解决方法我又想了方法,就是一直检测##Reuslt创建完没,创建完了才执行后面的操作,但是还是失败了Exec (@SQLStatement)While Not Exists(Select Name from Tempdb..sysObjects where Name='##Reuslt')
Begin
WAITFOR DELAY '00:00:01'
EndWAITFOR DELAY '00:02:00'
IF Exists (select Name from SysObjects where Name='ItemCSC') Drop Table ItemCSC
Exec ('Select * Into ItemCSC from ##Reuslt')
WHILE @B
BEGIN
WAITFOR('00:10.00')
IF NOT EXISTS(SELECT 1 FROM TB_TEMP_LOCK)
BEGIN
@B=FALSE
END
END
那就得用Drop Table ... 然后再 Create table
嗯 是笔误,写好的存储过程是正确的
IF Exists (select Name from Tempdb..SysObjects where Name='##Reuslt') Drop Table ##Reuslt
IF Exists (select Name from Tempdb..SysObjects where Name='##Reuslt')
exec('Drop Table ##Reuslt ')
对于全局临时表 ##Reuslt ,任何进程都可以删除的,如果其他进程删除了,就可能出现问题除非你的表名也是动态的。
declare @Tablename nvarchar(300)
set @Tablename = '##' + HOST_NAME() + replace(replace(replace(replace(convert(varchar(23),GETDATE(),121),'.',''),':',''),' ',''),'-','')
IF Exists (select Name from Tempdb..SysObjects where Name=@Tablename)
exec('Drop Table ' + @Tablename)
Select @SQLStatement=@SQLStatement+' Into ' + @Tablename + ' ' + SQLStatement from GridSQLStatement where Formname='ItemSellInfo' And GridName='Detail2'/*组合后执行,由于表数量过多,数据量大,所以组合后执行时间大概为60秒以上*/ Exec (@SQLStatement) IF Exists (select Name from SysObjects where Name='ItemCSC') Drop Table ItemCSC
Exec ('Select * Into ItemCSC from ' + @Tablename)
最后长远的写法还是只有2楼说的
begin
if object_id('tempdb..##Reuslt') is not null
break
endif exists(select 1 from sysObjects where Name='ItemCSC')
drop table ItemCSCexec('Select * Into ItemCSC from ##Reuslt')
begin
if object_id('tempdb..##Reuslt') is not null
break
endif exists(select 1 from sysObjects where Name='ItemCSC')
drop table ItemCSCexec('if object_id(''tempdb..##Reuslt'') is not null begin select * Into ItemCSC from ##Reuslt end')
exec(@SQLStatement)while object_id('tempdb..##Reuslt') is null
begin
if object_id('tempdb..##Reuslt') is not null
break
else
WAITFOR DELAY '00:00:10' --等待10秒,稍后再试
endif exists(select 1 from sysObjects where Name='ItemCSC')
drop table ItemCSCexec('if object_id(''tempdb..##Reuslt'') is not null begin select * Into ItemCSC from ##Reuslt end')