CODEWITHSUNDEEP
sqlsql-serverstored-procedures
Snake Eyes
Snake Eyes

Reputation: 16764

It is possible for created temporary table by a stored procedure to be used in another stored procedure?

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.

Upvotes: 2

Views: 5679

Answers (3)

Jitendra Gupta
Jitendra Gupta

Reputation: 824

There are two types of temporary tables a) temporary table and b) global temporary table.

Global temporary table (declared with ## like ##globalTempTable) can be used across all the procedures.

Normal temporary tables (declare with # like #temptale) can be used in nested procedures. If a sproc sp_A calls another sproc sp_B, a temp table declared in sp_A will be available in sp_B also.

Upvotes: 0

Joe G Joseph
Joe G Joseph

Reputation: 24046

Use GLOBAL temp tables

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.

Edit:

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

Upvotes: 4

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239704

If your first stored procedure calls the second stored procedure, the second procedure can access any temp tables that the first has created:

create procedure SP_1
as
    create table #T1 (ID int not null)
    insert into #T1 (ID) values (1),(2)

    exec SP_2
go
create procedure SP_2
as
    select * from #T1
go
exec SP_1

Produces two rows. However, I'm not sure if that's what you're trying to do - your example only defines a temp table and never accesses it.

Upvotes: 2

Related Questions