Use (insert and select) temp table in the store procedure-CodePudding

I use below code to create procedure to using temp table

Go
create procedure testTempTable
as
    INSERT INTO #resultTbl (code,userName) SELECT code,userName FROM Customer
    select * from #resultTbl
Go

When I want to run the procedure with exec testTempTable says

Invalid object name '#resultTbl'.

How can I use temp table in the procedure?

CodePudding user response：

Because your temp table might not be created, so you can't get result set from #resultTbl. you can try to use SELECT ... INTO temp table or create a temp table before you use it.

create procedure testTempTable
as
BEGIN
    SELECT code,userName 
    INTO #resultTbl
    FROM Customer
    
    SELECT * 
    FROM #resultTbl
    
END
Go