Cannot pass temporary table from one stored proced

问题:

I have a temporary table which is populated with a bunch of ints. I need to pass this populated temporary table into another stored procedure - however SQL does not allow you to pass a temporary table to another stored procedure (not nicely anyway).

I have created a TYPE to get around this as per https://msdn.microsoft.com/en-us/library/bb510489.aspx but intellisense is complaining that the table that uses this user-defined type is not a scalar variable.

CREATE TYPE TableType AS TABLE
(
    TheIds INT
);

DECLARE @IDsThatNeedToPass AS TableType


INSERT INTO @IDsThatNeedToPass 
SELECT . . .
. . .

EXEC OtherStoredProcedure @IDsThatNeedToPass 

For some reason the EXEC command says that I 'Must declare the scalar variable "@IDsThatNeedToPass"'

In addition to this, my OtherStoredProcedure cannot find my user-defined type.

CREATE PROCEDURE [dbo].[OtherStoredProcedure]
(
    @TheIds TableType READONLY -- Complains that Parameter or variable '@TheIds' has an invalid data type
)
...

Any ideas what could be causing this? I am using Microsoft SQL Server Management Studio 2014.

回答1:

Refer below one format of temp tables 

**Table variables (DECLARE @t TABLE)** are visible only to the connection 
that creates it, and are deleted when the batch or stored procedure ends.

**Local temporary tables (CREATE TABLE #t)** are visible only to the 
connection that creates it, and are deleted when the connection is closed.

**Global temporary tables (CREATE TABLE ##t)** are visible to everyone, and 
are deleted when all connections that have referenced them have closed.

**Tempdb permanent tables (USE tempdb CREATE TABLE t)** are visible to 
everyone,and are deleted when the server is restarted.