Каково правило области видимости для временных таблиц в exec в хранимых процедурах?

Сравните следующие хранимые процедуры:

CREATE PROCEDURE testProc1
AS
    SELECT * INTO #temp FROM information_schema.tables
    SELECT * FROM #temp
GO

CREATE PROCEDURE testProc2
AS
    EXEC('SELECT * INTO #temp FROM information_schema.tables')
    SELECT * FROM #temp
GO

Теперь, если я запускаю testProc1, он работает, а #temp, кажется, существует только во время этого вызова. Однако testProc2, похоже, вообще не работает, так как вместо этого я получаю сообщение об ошибке Invalid object name '#temp'.

Почему такое различие и как я могу использовать временную таблицу для SELECT * INTO, если имя исходной таблицы является параметром хранимой процедуры и может иметь произвольную структуру?

Обратите внимание, что я использую Microsoft SQL Server 2005.


sql exec stored-procedures tsql temp-tables
person polygenelubricants    schedule 10.05.2011    source источник

Ответы (2)


arrow_upward
6
arrow_downward

Из БОЛ:

Локальные временные таблицы видны только в текущем сеансе... ... Временные таблицы автоматически удаляются, когда они выходят за пределы области действия, если только явно не удалено с помощью DROP TABLE

Различие между вашей первой и второй процедурами заключается в том, что в первой таблица определяется в той же области, из которой она выбрана; во втором EXEC() создает таблицу в своей области, поэтому в этом случае выбор не выполняется...

Однако обратите внимание, что следующее работает отлично:

CREATE PROCEDURE [dbo].[testProc3]
AS
    SELECT * INTO #temp FROM information_schema.tables
    EXEC('SELECT * FROM #temp')
GO

И это работает, потому что область действия EXEC является дочерней областью действия хранимой процедуры. Когда таблица создается в родительской области, она также существует для любой из дочерних.

Чтобы дать вам хорошее решение, нам нужно больше узнать о проблеме, которую вы пытаетесь решить... но, если вам просто нужно выбрать из созданной таблицы, выполнение выбора в дочерней области работает отлично :

CREATE PROCEDURE [dbo].[testProc4]
AS
    EXEC('SELECT * INTO #temp FROM information_schema.tables; SELECT * FROM #temp')
GO
person Michael Fredrickson    schedule 10.05.2011

arrow_upward
0
arrow_downward

Вы можете попробовать использовать глобальную временную таблицу (с именем ##temp, а не #temp). Однако имейте в виду, что другие подключения также могут видеть эту таблицу.

person HLGEM    schedule 10.05.2011