一个存储过程创建的临时表是否可以在另一个存储过程中使用?

问题描述：

我有一个存储过程，它通过使用 CONTAINSTABLE 像(我放了一段存储过程代码)来创建一个带有找到的索引的临时表:

I have a stored procedure which creates a temporary table with found indexes by using CONTAINSTABLE like (I put a piece of stored procedure code):

CREATE TABLE #tmpTable(
  ID INT,
  RANK INT)

 SELECT @query = ' 
  SELECT 
    DISTINCT ID AS T_ID,
   indexTable.RANK AS RANK
  FROM 
   MyTable
  INNER JOIN
   CONTAINSTABLE(MyTable, (*), "ISABOUT('example*')") AS indexTable
  ON
   MyTable.ID = indexTable.[KEY]
  ORDER BY RANK DESC'

如果可能，我想将临时表用于另一个存储过程，以将其值用于其他目的，并避免做两次相同的事情.

I want to use, if is possible, the temporary table into another stored procedure to use its values for other purpose and avoid to do twice same thing.

如果不可能，那么您能否建议我在不同的存储过程中重用表数据的最佳方法.另外，我知道我无法在存储过程中创建视图，因此视图不在讨论范围内.

If is not possible then can you advice me the best way to re-use table data in different stored procedure. Also, I know that I cannot create view inside stored procedure then view is out of discussion.

答

使用全局临时表

CREATE TABLE ##tmpTable(
  ID INT,
  RANK INT)

您可以通过在表名前加上双哈希(##)来创建全局临时表

You can create a global temporary table by prefixing the table name with double hash(##)

一旦此表由连接创建，就像永久表一样，任何用户都可以通过任何连接使用它.只有在所有连接都关闭后才能删除它.

Once this table has been created by a connection, like a permanent table it is then available to any user by any connection. It can only be deleted once all connections have been closed.

为了检查临时表是否存在，您可以使用以下语句/检查.

In order to check for the existence of a temporary table you can use the following statement/check.

if object_id('tempdb..##tmpTable') is not null
begin
    drop table ##tmpTable
end

一个存储过程创建的临时表是否可以在另一个存储过程中使用?

相关推荐