Can't store procedure into dynamic temp table

Question

I have a stored procedure that returns columns . This stored procedure is mainly being used by other query for functional reasons

So my stored procedure:

IF OBJECT_ID ( 'dbo.ProcDim', 'P' ) IS NOT NULL 
    DROP PROCEDURE dbo.ProcDim;
GO
CREATE PROCEDURE dbo.ProcDim
                            @Dim1 nvarchar(50), 
                            @Dim2 nvarchar(50) 
AS 
    SET NOCOUNT ON;
    IF OBJECT_ID('tempdb..#TMP1') IS NOT NULL
    DROP TABLE #TMP1

    SELECT  
    INTO    #TMP1           
    FROM  DBase.dbo.Table1  AS Parameter
    WHERE Parameter.Dim1    = @Dim1 
    AND   Parameter.Dim2    = @Dim2;
GO

EXECUTE dbo.ProcDim N'value1', N'value2';
SELECT * from #TMP1

So when i excute my procedure without #TMP1 work fine but i want to insert the result into temp table

score 6 · Accepted Answer · answered Jan 21 '16 at 12:15

6

You can't use temporary table in such a manner.

By this code: SELECT INTO #TMP1 you're implicity creating temporary table, and it is accessible in the scope of your stored procedure - but not outside of this scope.

If you need this temporary table to be accessible outside of stored procedure, you have to remove INTO #TMP1 from stored procedure and explicitly create it outside:

create table #tmp1 (columns_definitions_here)

insert into #tmp1
exec  dbo.ProcDim N'value1', N'value2';

select * from #TMP1

Notice you have to explicitly create temporary table in this case, supplying all column names and their data types.

Alternatively you can change your stored procedure to be user-defined table function, and in this case you will be able to implicitly create and populate your temporary table:

create function dbo.FuncDim
(
    @Dim1 nvarchar(50), 
    @Dim2 nvarchar(50)
)
returns @result TABLE (columns_definition_here) 
as
begin
    ... your code
   return
end

select  *
into  #TMP1
from dbo.FuncDim(@Dim1, @Dim2)

answered Jan 21 '16 at 12:15

Andrey Korneyev

26,353
15
70
71

1

SQL Server function does not use stored execution plan. it causes performance issues. You should use a stored procedure in such cases. And if you are using a Stored procedure that calls another stored procedure inside it then the result cant be stored into Temp Table. So the solution to the probelm is a global temp table :) – Ambareesh Surendran Jan 21 '16 at 12:21
1

While multi-statement user-defined functions definitely doesn't has stored execution plans - it is "less evil" than global temporary tables from my point of view. You will face performance issue due to lack of saved execution plan only in the case of very complex statements in your function taking too long to create execution plan "on the fly". Anyway, it is only one of alternative solutions proposed in my answer. – Andrey Korneyev Jan 21 '16 at 12:28
Thank you for this importent explanation. – stoner Jan 21 '16 at 12:28

score 2 · Answer 2 · answered Jan 21 '16 at 12:16

2

Temp tables are scoped (in this case) to the stored procedure in which they're created. That is, when your stored procedure completes, the temp table is dropped.

If you need the contents of the temp table, select from it before the end of the procedure - IOW, select * from #TMP1 should be the output of the procedure, not a separate statement executed outside of it.

answered Jan 21 '16 at 12:16

alroc

27,574
6
51
97

Yes you are right the temp table is dropped when i stored it. – stoner Jan 21 '16 at 12:26
This [question](http://stackoverflow.com/questions/653714/insert-results-of-a-stored-procedure-into-a-temporary-table) shows a few different techniques you can use to capture the result outside of the SP, if required. – David Rushton Jan 21 '16 at 12:29

score 1 · Answer 3 · answered Jan 21 '16 at 13:16

While @AndyKorneyev is good enough, there're some caveats in it. For example, you have to keep structure of the table and dataset returned by procedure synchronized, also insert into exec calls cannot be nested.

So, as far as I know - there're no silver bullet in terms of sharing data between procedures, so you have to think about best suitable solution for you case.

Just to give your more information about topic - here's nice article by Sommarskog concerning sharing data between stored procedures.

For example, I also sometimes use 'shared table' solution (be warned it could lead to recompilations):

create procedure dbo.p_test
as
begin
    set nocount on

    insert into #temp_shared (col1, col2)
    select col1, col2 from <...>
end
go

-- creating table so it'll be used inside temp
create table #temp ...

exec dbo.p_test

-- now you have data in your table
select * from #temp

score 0 · Answer 4 · answered Jan 21 '16 at 12:14

0

--#TMP1 is a local temp table its, Scope is limited to its query layer only.

if you want to access it out side its query layer use global temp table as ##TMP1

IF OBJECT_ID ( 'dbo.ProcDim', 'P' ) IS NOT NULL 
    DROP PROCEDURE dbo.ProcDim;
GO
CREATE PROCEDURE dbo.ProcDim
                            @Dim1 nvarchar(50), 
                            @Dim2 nvarchar(50) 
AS 
    SET NOCOUNT ON;
    IF OBJECT_ID('tempdb..##TMP1') IS NOT NULL
    DROP TABLE ##TMP1

    SELECT  
    INTO    ##TMP1           
    FROM  DBase.dbo.Table1  AS Parameter
    WHERE Parameter.Dim1    = @Dim1 
    AND   Parameter.Dim2    = @Dim2;
GO

EXECUTE dbo.ProcDim N'value1', N'value2';
SELECT * from ##TMP1

answered Jan 21 '16 at 12:14

Ambareesh Surendran

508
2
12

1

Usage of global temporary table can't be considered as a good practice and should be avoided in most cases since it can lead to the really unexpected results due to concurent requests modifying it. – Andrey Korneyev Jan 21 '16 at 12:17
@AndyKorneyev the last time i checked, there is a mechanism called "LOCKING" in RDBMS that make sure that no unexpected results will be created due to concurrent access to a table. so dont worry about that. – Ambareesh Surendran Jan 21 '16 at 12:34
So you prefere to work with global temp table, but wrap all manipulations into transaction with, say, snapshot or serializable isolation level to be sure noone will change the data you're working with? Well.... good luck. ;) – Andrey Korneyev Jan 21 '16 at 12:43
@AndyKorneyev I am pretty sure that you don't know anything about Implicit transactions and you are OK with allowing dirty read,who would hire some like that. – Ambareesh Surendran Jan 21 '16 at 12:53
@AmbareeshSurendran if we're still talking about *temporary* tables - then what dirty reads are you talking about in the case of *local* temporary table? – Andrey Korneyev Jan 21 '16 at 12:57
1

@AndyKorneyev You dont know anythhing about concurrency in a global temporary tables ,read about it http://www.sqlnotes.info/2012/01/30/sharing-global-temp-tables/ – Ambareesh Surendran Jan 21 '16 at 12:59
@AmbareeshSurendran don't know how locking will help you, imagine what will happen if you call the procedure from 2 different calls simultaneously. That said - I've never used global temp tables in the production code and never seen good case for using it. – Roman Pekar Jan 21 '16 at 13:33
@RomanPekar Same thing will happen when you call the procedure (that perform update,insert or delete on a permanent table) from 2 different calls simultaneously – Ambareesh Surendran Jan 21 '16 at 13:37

score 0 · Answer 5 · answered Jan 21 '16 at 12:25

Temp tables are scoped to current connection. So that is the reason why you are not able to get the result.

If you want query the temp table, then query in procedure itself.

IF OBJECT_ID ( 'dbo.ProcDim', 'P' ) IS NOT NULL 
DROP PROCEDURE dbo.ProcDim;
GO
CREATE PROCEDURE dbo.ProcDim
                        @Dim1 nvarchar(50), 
                        @Dim2 nvarchar(50) 
AS 
SET NOCOUNT ON;
IF OBJECT_ID('tempdb..#TMP1') IS NOT NULL
DROP TABLE #TMP1

SELECT  *
INTO    #TMP1           
FROM  DBase.dbo.Table1  AS Parameter
WHERE Parameter.Dim1    = @Dim1 
AND   Parameter.Dim2    = @Dim2;

---Here write query to fetch the data from temp table.
SELECT * FROM  #TMP1 

GO

Now give a try to execute statement.

  EXECUTE dbo.ProcDim N'value1', N'value2';

I don't prefer Stored procedures to use in this way. For your requirement you can go for Functions instead.

Can't store procedure into dynamic temp table

5 Answers5