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.