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?
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...
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.