Unable to use a temporary table more than once [closed]

Posted on

Question :

I am using SQL Server 2008 to build a stored procedure. In it, I create a temporary table (#results) using recursion for getting all the ancestors given a specific record.

My specific problem is, when I try to use this #results table twice, the second time it returns this message:

Invalid object name ‘#results’.

Here is my stored procedure:

SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[usp_GetSurveyByWorkflow]   
 @WorkflowId INT  = NULL  
AS      
BEGIN   
 DECLARE @SurveyService int = NULL;
 DECLARE @Theme int = NULL;
 DECLARE @TopAncestor int = NULL;
 DECLARE @Survey int = NULL;

 SET NOCOUNT ON
 SELECT @Theme = THEMEID FROM [dbCSSA].[dbo].[TYPESOFSERVICES] WHERE TYPEOFSERVICEID = @WorkflowId;              
;WITH #results AS
(
    SELECT  THEMEID, 
            THEMEIDPARENT 
    FROM    [dbCSSA].[dbo].[THEMES]
    WHERE   THEMEID = (@Theme)
    UNION ALL
    SELECT  t.THEMEID, t.THEMEIDPARENT FROM [dbCSSA].[dbo].[THEMES] t
            INNER JOIN #results r ON r.THEMEIDPARENT = t.THEMEID
)
SELECT @SurveyService = IDSERVICESURVEYS FROM [dbCSSA].[dbo].[SURVEYSERVICES] WHERE IDTHEMESERVICE =
    (SELECT THEMEID FROM #results WHERE THEMEIDPARENT IS NULL);
--It fails here:
SELECT @TopAncestor = THEMEID FROM #results WHERE THEMEIDPARENT IS NULL;
SELECT @Survey = SURVEYID FROM [dbCSSA].[dbo].[SURVEYSERVICES] WHERE IDTHEMESERVICE = @TopAncestor;
SELECT * FROM FORMSURVEYS
    INNER JOIN FIELDSURVEYS ON FORMSURVEYS.IDFORM = FIELDSURVEYS.FORMID 
    FULL JOIN OPTIONVALUESFIELDSURVEYS ON FIELDSURVEYS.IDFIELD = OPTIONVALUESFIELDSURVEYS.IDFIELD
    WHERE FORMSURVEYS.SURVEYID = @Survey
END

What am I doing wrong?

Answer :

Community wiki answer generated from comments to the question by various people.

Despite the name, you are declaring #results as a Common Table Expression (CTE), not a temporary table. CTEs only apply to the same statement they are defined in.

You can solve your problem using a temporary table, using syntax like:

CREATE TABLE #mytable (<columns>); 
INSERT INTO #mytable...

or

SELECT * 
INTO #results
FROM ...

Additional comments by the author:

As Pointed out by Shanky, I am using a CTE instead of a temp table, which as Queue Man said, loses scope when I assign it to a variable.

So I ended up creating a temp table and inserting the contents of my CTE #results as suggested by JNK.

Leave a Reply

Your email address will not be published.