当创建#TEMP表时，“作用域”大于创建它的过程。
下面是一个示例：
IF EXISTS
(
SELECT * FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = N'PROCEDURE' and ROUTINE_SCHEMA = N'dbo' and ROUTINE_NAME = N'uspProc002'
)
BEGIN
DROP PROCEDURE [dbo].[uspProc002]
END
GO
CREATE Procedure dbo.uspProc002
AS
BEGIN
/* Uncomment this code if you want to be more explicit about bad "wiring" */
/*
IF OBJECT_ID('tempdb..#TableOne') IS NULL
begin
THROW 51000, 'The procedure expects a temp table named #TableOne to already exist.', 1;
end
*/
/* Note, I did not Create #TableOne in this procedure. It "pre-existed". An if check will ensure that it is there. */
IF OBJECT_ID('tempdb..#TableOne') IS NOT NULL
begin
Insert into #TableOne ( SurrogateKey , NameOf ) select 2001, 'uspProc002'
end
END
GO
IF EXISTS
(
SELECT * FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = N'PROCEDURE' and ROUTINE_SCHEMA = N'dbo' and ROUTINE_NAME = N'uspProc001'
)
BEGIN
DROP PROCEDURE [dbo].[uspProc001]
END
GO
CREATE Procedure dbo.uspProc001 (
@Param1 int
)
AS
BEGIN
IF OBJECT_ID('tempdb..#TableOne') IS NOT NULL
begin
drop table #TableOne
end
CREATE TABLE #TableOne
(
SurrogateKey int ,
NameOf varchar(12)
)
Insert into #TableOne ( SurrogateKey , NameOf ) select 1001, 'uspProc001'
Select * from #TableOne
EXEC dbo.uspProc002
Select * from #TableOne
IF OBJECT_ID('tempdb..#TableOne') IS NOT NULL
begin
drop table #TableOne
end
END
GO
exec dbo.uspProc001 0
602392714
清零编程群