我第一次执行存储过程 创建临时表 里面 添加了 10条数据,然后 我在删除 临时表的前3条数据 ,临时表还剩 7条数据,我第二次执行存储过程还想用临时表剩下的 7条数据 怎么写呢? -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- DECLARE @NUM varchar(50) DECLARE @temptablename varchar(50)set @temptablename= 'CRM_INF_Verification_Q_'+@Agentid print @temptablename if not exists (select * from dbo.sysobjects where id = object_id(@temptablename) and OBJECTPROPERTY(id, N'IsUserTable') = 1) begin exec ('create table [dbo].['+@temptablename+'] ( ID int, InType varchar(50), NUM varchar(50), Question varchar(50), IsUse int, CreateTime datetime, CreateUser varchar(50) ) ') exec (' insert into [dbo].['+@temptablename+'] (ID,InType,NUM,Question,IsUse,CreateTime,CreateUser) select ID,InType,NUM,Question,IsUse,CreateTime,CreateUser from CRM_INF_Verification where Intype=''商户'' order by newID()') end else begin Select @NUM=Count(*) from ['+@temptablename+'] if (@NUM=0) begin exec (' insert into [dbo].['+@temptablename+'] (ID,InType,NUM,Question,IsUse,CreateTime,CreateUser) select ID,InType,NUM,Question,IsUse,CreateTime,CreateUser from CRM_INF_Verification where Intype=''商户'' order by newID()') end end if(1=1) exec(' select top 3 ID,Question from @temptablename') ; 能告诉我一下 为什么红色部分报了个 无效的错呢
sql server临时表是会话级的,不同的会话是透明的!
即使同一个会话,你多次执行,初始临时表数据都会清理掉的!
我第一次执行存储过程 创建临时表 里面 添加了 10条数据,然后 我在删除 临时表的前3条数据 ,临时表还剩 7条数据,我第二次执行存储过程还想用临时表剩下的 7条数据 怎么写呢?
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
DECLARE @NUM varchar(50)
DECLARE @temptablename varchar(50)set @temptablename= 'CRM_INF_Verification_Q_'+@Agentid
print @temptablename
if not exists (select * from dbo.sysobjects where id = object_id(@temptablename) and OBJECTPROPERTY(id, N'IsUserTable') = 1)
begin
exec ('create table [dbo].['+@temptablename+']
(
ID int,
InType varchar(50),
NUM varchar(50),
Question varchar(50),
IsUse int,
CreateTime datetime,
CreateUser varchar(50)
) ')
exec (' insert into [dbo].['+@temptablename+'] (ID,InType,NUM,Question,IsUse,CreateTime,CreateUser)
select ID,InType,NUM,Question,IsUse,CreateTime,CreateUser from CRM_INF_Verification where Intype=''商户'' order by newID()')
end
else
begin
Select @NUM=Count(*) from ['+@temptablename+']
if (@NUM=0)
begin
exec (' insert into [dbo].['+@temptablename+'] (ID,InType,NUM,Question,IsUse,CreateTime,CreateUser)
select ID,InType,NUM,Question,IsUse,CreateTime,CreateUser from CRM_INF_Verification where Intype=''商户'' order by newID()')
end
end
if(1=1)
exec(' select top 3 ID,Question from @temptablename') ;
能告诉我一下 为什么红色部分报了个 无效的错呢