如何将查询结果存储在存储过程的临时表中

Question

我正在尝试将复杂查询的结果存储在临时表中，但不断收到错误。下面是我的存储过程中的代码：

DECLARE @TempItems TABLE
(
ID int IDENTITY, ForumThreadID int, ForumID int, ParentID int, title NVARCHAR(MAX), title_path NVARCHAR(MAX), 
level_id NVARCHAR(MAX), level_id_path NVARCHAR(MAX), PostBody NVARCHAR(MAX), CreatedBy int, UserName NVARCHAR(50), Created DateTime
)
DECLARE @SQL NVARCHAR(MAX)
SET @SQL = 'WITH TreeList (ForumThreadID, ForumID, ParentID, title, title_path, level_id, level_id_path) as 
    ( 
    SELECT p.ForumThreadID,  
    p.ForumID, 
    p.ParentID,  
    p.PostSubject,  
    CONVERT(nvarchar(max), p.ForumThreadID),  
    ROW_NUMBER() OVER(PARTITION BY ParentID ORDER BY p.ForumThreadID),  
    RIGHT(''0000'' + CAST(ROW_NUMBER() OVER(PARTITION BY ParentID ORDER BY p.ForumThreadID) AS varchar(max)),4)  
    FROM ForumThreads p 
    WHERE (p.ParentID = ' + @ParentID + ') AND (p.Deleted IS NULL) 
    UNION ALL 
    SELECT c.ForumThreadID,  
    c.ForumID,  
    c.ParentID,  
    c.PostSubject,  
    r.title_path + ''/'' + CAST(c.ForumThreadID AS VARCHAR(MAX)),  
    ROW_NUMBER() OVER(PARTITION BY c.ParentID ORDER BY c.ForumThreadID),  
    CONVERT(varchar(max), r.level_id_path + ''.'' + RIGHT(''0000'' + CAST(ROW_NUMBER() OVER(PARTITION BY c.ParentID ORDER BY c.ForumThreadID) AS VARCHAR),4))  
    FROM ForumThreads AS c 
    INNER JOIN treelist AS r 
    ON c.ParentID = r.ForumThreadID 
    WHERE (c.Deleted IS NULL)) 
    SELECT TOP 100 TreeList.*, d.PostBody, d.CreatedBy, Members.UserName, COALESCE(d.Created,''1-JAN-1900'') AS Created 
    FROM TreeList INNER JOIN ForumThreads AS d ON TreeList.ForumThreadID = d.ForumThreadID INNER JOIN 
    Members ON d.CreatedBy = Members.MemberID 
    WHERE (d.Deleted IS NULL) 
    ORDER BY level_id_path;'
INSERT INTO @TempItems (ForumThreadID, ForumID, ParentID, title, title_path, level_id, level_id_path, PostBody, CreatedBy, UserName, Created) EXEC @SQL
SELECT * FROM @TempItems

我得到的错误是：

Msg 203, Level 16, State 2, Procedure spPagedForumThreads, Line 53
The name 'WITH TreeList (ForumThreadID, ForumID, ParentID, title, title_path, level_id, level_id_path) as 
( 
SELECT p.ForumThreadID,  
p.ForumID, 
p.ParentID,  
p.PostSubject,  
CONVERT(nvarchar(max), p.ForumThreadID),  
ROW_NUMBER() OVER(PARTITION BY ParentID ORDER BY p.ForumThreadID),  
RIGHT('0000' + CAST(ROW_NUMBER() OVER(PARTITION BY ParentID ORDER BY p.ForumThreadID) AS varchar(max)),4)  
FROM ForumThreads p 
WHERE (p.ParentID = 10720) AND (p.Deleted IS NULL) 
UNION ALL 
SELECT c.ForumThreadID,  
c.ForumID,  
c.ParentID,  
c.PostSubject,  
r.title_path + '/' + ' is not a valid identifier.

我做错了什么？

有没有另一种方法可以创建临时表，不需要将查询另存为字符串？

谢谢！

Answer 1

执行动态 SQL 的目的是什么？ 在 CTE 之后，您应该能够修改您的 SELECT 语句以INSERT INTO @TempItems SELECT TOP 100 TreeList.* .....

这应该在不需要动态 SQL 的情况下工作（即 EXEC ）。 请注意，WITH前面的语句需要以分号结尾才能在语法上有效。

DECLARE @TempItems TABLE
(
ID int IDENTITY, ForumThreadID int, ForumID int, ParentID int, title NVARCHAR(MAX), title_path NVARCHAR(MAX), 
level_id NVARCHAR(MAX), level_id_path NVARCHAR(MAX), PostBody NVARCHAR(MAX), CreatedBy int, UserName NVARCHAR(50), Created DateTime
);
WITH TreeList (ForumThreadID, ForumID, ParentID, title, title_path, level_id, level_id_path) as 
    ( 
    SELECT p.ForumThreadID,  
    p.ForumID, 
    p.ParentID,  
    p.PostSubject,  
    CONVERT(nvarchar(max), p.ForumThreadID),  
    ROW_NUMBER() OVER(PARTITION BY ParentID ORDER BY p.ForumThreadID),  
    RIGHT('0000' + CAST(ROW_NUMBER() OVER(PARTITION BY ParentID ORDER BY p.ForumThreadID) AS varchar(max)),4)  
    FROM ForumThreads p 
    WHERE (p.ParentID = ' + @ParentID + ') AND (p.Deleted IS NULL) 
    UNION ALL 
    SELECT c.ForumThreadID,  
    c.ForumID,  
    c.ParentID,  
    c.PostSubject,  
    r.title_path + '/' + CAST(c.ForumThreadID AS VARCHAR(MAX)),  
    ROW_NUMBER() OVER(PARTITION BY c.ParentID ORDER BY c.ForumThreadID),  
    CONVERT(varchar(max), r.level_id_path + '.' + RIGHT('0000' + CAST(ROW_NUMBER() OVER(PARTITION BY c.ParentID ORDER BY c.ForumThreadID) AS VARCHAR),4))  
    FROM ForumThreads AS c 
    INNER JOIN treelist AS r 
    ON c.ParentID = r.ForumThreadID 
    WHERE (c.Deleted IS NULL)) 
INSERT INTO @TempItems (ForumThreadID, ForumID, ParentID, title, title_path, level_id, level_id_path, PostBody, CreatedBy, UserName, Created)
    SELECT TOP 100 TreeList.*, d.PostBody, d.CreatedBy, Members.UserName, COALESCE(d.Created,'1-JAN-1900') AS Created 
    FROM TreeList INNER JOIN ForumThreads AS d ON TreeList.ForumThreadID = d.ForumThreadID INNER JOIN 
    Members ON d.CreatedBy = Members.MemberID 
    WHERE (d.Deleted IS NULL) 
    ORDER BY level_id_path;

SELECT * FROM @TempItems

Answer 2

EXEC(@SQL)而不仅仅是EXEC @SQL。