一个存储过程创建的临时表是否可以在另一个存储过程中使用? [英] It is possible for created temporary table by a stored procedure to be used in another stored procedure?

问题描述

我有一个存储过程，它通过使用 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

这篇关于一个存储过程创建的临时表是否可以在另一个存储过程中使用?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！