从SP和临时表中选择值

I have a Stored Procedure in MSSQL 2008, inside of this i've created a Temporary Table, and then i executed several inserts into the temporary Table. How can i select all the columns of the Temporary Table outside the stored procedure? I Mean, i have this:

我在MSSQL 2008中有一个存储过程,在此我创建了一个临时表,然后我在临时表中执行了几次插入。如何选择存储过程外的临时表的所有列?我的意思是,我有这个:

CREATE PROCEDURE [dbo].[LIST_CLIENTS]

    CREATE TABLE #CLIENT(
         --Varchar And Numeric Values goes here
     )

  /*Several Select's and Insert's against the Temporary Table*/

  SELECT * FROM #CLIENT

END

In another Query i'm doing this:

在另一个查询我这样做:

sp_configure 'Show Advanced Options', 1 
GO
RECONFIGURE
GO

sp_configure 'Ad Hoc Distributed Queries', 1 
GO
RECONFIGURE
GO

    SELECT * 
    INTO #CLIENT 
    FROM OPENROWSET
    ('SQLOLEDB','Server=(local);Uid=Cnx;pwd=Cnx;database=r8;Trusted_Connection=yes;
    Integrated Security=SSPI',
    'EXEC dbo.LIST_CLIENTS ''20110602'', NULL, NULL, NULL, NULL, NULL')

But i get this error:

但我得到这个错误:

Msg 208, Level 16, State 1, Procedure LIST_CLIENTS, Line 43
Invalid object name '#CLIENT'.

I've tried with Global Temporary Tables and It doesn't work. I know that is the scope of the temporary table, but, how can i get the table outside the scope of the SP?

我尝试过使用全局临时表,它不起作用。我知道这是临时表的范围,但是,我怎样才能使表超出SP的范围?

Thanks in advance

提前致谢

6 个解决方案

#1

I think there is something deeper going on here.

我认为这里有更深层次的东西。

One idea is to use a table variable inside the stored procedure instead of a #temp table (I have to assume you're using SQL Server 2005+ but it's always nice to state this up front). And use OPENQUERY instead of OPENROWSET. This works fine for me:

一个想法是在存储过程中使用表变量而不是#temp表(我必须假设您使用的是SQL Server 2005+,但最好先说明这一点)。并使用OPENQUERY而不是OPENROWSET。这对我来说很好:

USE tempdb;
GO
CREATE PROCEDURE dbo.proc_x
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @x TABLE(id INT);
    INSERT @x VALUES(1),(2);
    SELECT * FROM @x;
END
GO

SELECT *
  INTO #client 
  FROM OPENQUERY
  (
    [loopback linked server name], 
    'EXEC tempdb.dbo.proc_x'
  ) AS y;

SELECT * FROM #client;

DROP TABLE #client;

DROP PROCEDURE dbo.proc_x;

Another idea is that perhaps the error is occurring even without using SELECT INTO. Does the stored procedure reference the #CLIENT table in any dynamic SQL, for example? Does it work when you call it on its own or when you just say SELECT * FROM OPENROWSET instead of SELECT INTO? Obviously, if you are working with the #temp table in dynamic SQL you're going to have the same kind of scope issue working with a @table variable in dynamic SQL.

另一个想法是即使不使用SELECT INTO也可能发生错误。例如,存储过程是否在任何动态SQL中引用#CLIENT表?当你单独调用它或者只是说SELECT * FROM OPENROWSET而不是SELECT INTO时它是否有效?显然,如果您在动态SQL中使用#temp表,那么在动态SQL中使用@table变量时会遇到相同类型的范围问题。

At the very least, name your outer #temp table something other than #CLIENT to avoid confusion - then at least nobody has to guess which #temp table is not being referenced correctly.

至少,将外部#temp表命名为#CLIENT以外的其他内容以避免混淆 - 然后至少没有人必须猜测哪个#temp表没有被正确引用。

#2

Since the global temp table failed, use a real table, run this when you start your create script and drop the temp table once you are done to make sure.

由于全局临时表失败,请使用实际表,在启动创建脚本时运行此表,并在完成后删除临时表以确保。

IF OBJECT_ID('dbo.temptable', 'U') IS NOT NULL
BEGIN 
DROP TABLE dbo.temptable
END

CREATE TABLE dbo.temptable 
(    ... )

#3

You need to run the two queries within the same connection and use a global temp table.

您需要在同一连接中运行两个查询并使用全局临时表。

#4

In SQL Server 2008 you can declare User-Defined Table Types which represent the definition of a table structure. Once created you can create table parameters within your procs and pass them a long and be able to access the table in other procs.

在SQL Server 2008中,您可以声明用户定义的表类型,它表示表结构的定义。一旦创建,您可以在procs中创建表参数并将它们传递很长时间,并能够访问其他过程中的表。

#5

I guess the reason for such behavior is that when you call OPENROWSET from another server it firstly and separately requests the information about procedure output structure (METADATA). And the most interesting thing is that this output structure is taken from the first SELECT statement found in the procedure. Moreover, if the SELECT statement follows the IF-condition the METADATA request ignores this IF-condition, because there is no need to run the whole procedure - the first met SELECT statement is enough. (By the way, to switch off that behavior, you can include SET FMTONLY OFF in the beginning of your procedure, but this might increase the procedure execution time).

我猜这种行为的原因是,当你从另一台服务器调用OPENROWSET时,它首先单独请求有关过程输出结构(METADATA)的信息。最有趣的是,此输出结构取自过程中的第一个SELECT语句。此外,如果SELECT语句遵循IF条件,则METADATA请求会忽略此IF条件,因为不需要运行整个过程 - 第一个遇到的SELECT语句就足够了。 (顺便说一下,要关闭该行为,可以在过程开始时包括SET FMTONLY OFF,但这可能会增加过程执行时间)。

The conclusions:

— when the METADATA is being requested from a temp table (created in a procedure) it does not actually exists, because the METADATA request does not actually run the procedure and create the temp table.

- 当从临时表(在过程中创建)中请求METADATA时,它实际上不存在,因为METADATA请求实际上不运行该过程并创建临时表。

— if a temp table can be replaced with a table variable it solves the problem

- 如果临时表可以用表变量替换,它可以解决问题

— if it is vital for the business to use temp table, the METADATA request can be fed with fake first SELECT statement, like:

- 如果对业务使用临时表至关重要,则可以使用假的第一个SELECT语句来提供METADATA请求,如:

declare @t table(ID int, Name varchar(15));
if (0 = 1) select ID, Name from @t;         -- fake SELECT statement
create table #T (ID int, Name varchar(15));
select ID, Name from #T;                    -- real SELECT statement

— and one more thing is to use a common trick with FMTONLY (that is not my idea) :

- 还有一件事是使用FMTONLY的常见技巧(这不是我的想法):

declare @fmtonlyOn bit = 0;
if 1 = 0 set @fmtonlyOn = 1;
set fmtonly off;
create table #T (ID int, Name varchar(15));      
if @fmtonlyOn = 1 set fmtonly on;
select ID, Name from #T;

#6

The reason you're getting the error is because the temp table #Client was not declared before you ran the procedure to insert into it. If you declare the table, then execute the list proc and use direct insert -

您收到错误的原因是因为在您运行插入过程的过程之前未声明临时表#Client。如果声明表,则执行list proc并使用直接插入 -

INSERT INTO #Client

插入#Client

EXEC LIST_CLIENTS

#1