CREATE PROCEDURE Sp_AnaMarri @D_Start char(8), @D_End char(8)
AS
begin
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TblAnaMarri]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TblAnaMarri]
GO
exec ('
CREATE TABLE [dbo].[TblAnaMarri] (
[AM_Old] [varchar] (4) COLLATE Chinese_PRC_CI_AS NULL ,
[AM_WCount] [int] NULL ,
[AM_OCount] [int] NULL ,
[AM_OAverage] [numeric](5, 2) NULL
)
')....
end
go
AS
begin
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TblAnaMarri]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TblAnaMarri]
GO
exec ('
CREATE TABLE [dbo].[TblAnaMarri] (
[AM_Old] [varchar] (4) COLLATE Chinese_PRC_CI_AS NULL ,
[AM_WCount] [int] NULL ,
[AM_OCount] [int] NULL ,
[AM_OAverage] [numeric](5, 2) NULL
)
')....
end
go
[AM_Old] [varchar] (4) COLLATE Chinese_PRC_CI_AS NULL ,
[AM_WCount] [int] NULL ,
[AM_OCount] [int] NULL ,
[AM_OAverage] [numeric](5, 2) NULL
) ON [PRIMARY]select * from #TblAnaMarridrop table #TblAnaMarri
GO----exec CreateTempTab 沒有任何問題啊!
2、你创建的不是全局的临时表,所以该临时表会在创建它的会话结束时被释放,而存储过程就是与数据库的一次会话,所以存储过程结束后,临时表就自然被释放了,你到存储过程以外来引用肯定会引用不到的,在存储过程内部引用没有问题的。
你必须显示的删除drop table ##temp
它有并发问题!
用#局部临时表吧!
它的生命周期是会话!
...
select * from #临时表