Reuse results from SQL Server common table express

问题:

I have a query to retrieve all the modules and child modules for a page, using a common table expression. Is it possible to use the results from the cte more than once?

example

WITH top_level_modules (
[AppContentModuleID]
,[SortIndex]
,[ContentHolderName]
,[OwnerType]
,[AppContentModuleGuid]
,[parent_AppContentModuleID]
,[ModuleID]
,[RenderIDTag]
,[WrapperType]
,[Level]
)
AS
( 
SELECT amcp.[AppContentModuleID]
    ,amcp.[SortIndex]
    ,amcp.[ContentHolderName]
    ,1
    ,amc.[AppContentModuleGuid]
    ,amc.[parent_AppContentModuleID]
    ,amc.[ModuleID]
    ,amc.[RenderIDTag]
    ,amc.[WrapperType]
    ,0 AS [Level]
FROM [dbo].[application_module_content_page] amcp
INNER JOIN [dbo].[application_module_content] amc on amcp.[AppContentModuleID] = amc.[AppContentModuleID]
WHERE amcp.[PageID] = @PageID
UNION
SELECT amcm.[AppContentModuleID]
    ,amcm.[SortIndex]
    ,amcm.[ContentHolderName]
    ,2
    ,amc.[AppContentModuleGuid]
    ,amc.[parent_AppContentModuleID]
    ,amc.[ModuleID]
    ,amc.[RenderIDTag]
    ,amc.[WrapperType]
    ,0
FROM [dbo].[application_module_content_masterpage] amcm
INNER JOIN [dbo].[application_module_content] amc on amcm.[AppContentModuleID] = amc.[AppContentModuleID]
WHERE amcm.[AppMasterPageID] = @MasterPageID
),
child_modules AS 
(
SELECT tlm.[AppContentModuleID]
    ,tlm.[SortIndex]
    ,tlm.[ContentHolderName]
    ,tlm.[OwnerType]
    ,tlm.[AppContentModuleGuid]
    ,tlm.[parent_AppContentModuleID]
    ,tlm.[ModuleID]
    ,tlm.[RenderIDTag]
    ,tlm.[WrapperType]
    ,tlm.[Level]
FROM top_level_modules tlm
UNION ALL
SELECT 
    amc.[AppContentModuleID]
    ,CASE WHEN amc.[SortIndex] IS NULL THEN tlm.[SortIndex] ELSE amc.[SortIndex] END
    ,null
    ,3
    ,amc.[AppContentModuleGuid]
    ,amc.[parent_AppContentModuleID]
    ,amc.[ModuleID]
    ,amc.[RenderIDTag]
    ,amc.[WrapperType]
    ,[Level] + 1 AS [Level]
FROM [dbo].[application_module_content] amc
INNER JOIN child_modules tlm on tlm.[AppContentModuleID] = amc.[parent_AppContentModuleID]
)
SELECT * 
FROM child_modules cm
ORDER BY cm.[OwnerType]
, cm.[Level]
, cm.[SortIndex]

SELECT apcs.[StyleType] 
    ,apcs.[StyleName]
    ,apcs.[StyleValue]
FROM child_modules cm
INNER JOIN dbo.[application_module_content_style] apcs 
on cm.AppContentMdouleID = apcs.AppContentMdouleID

The first select works, but the second select throws up the error "Invalid object name 'child_modules'."

回答1:

From the WITH common_table_expression manual:

Specifies a temporary named result set, known as a common table expression (CTE). This is derived from a simple query and defined within the execution scope of a single SELECT, INSERT, UPDATE, or DELETE statement.

So, no, you can't extend the scope of the CTE beyond the SELECT statement it was defined in. You will have to store the result in a temporary table or table valued variable if you want to use the result more than once.

回答2:

You will need split child_modules CTE into two CTE's.

The first one containing the first SELECT of the current child_modules CTE, then use a second CTE that builds upon child_modules.

Your final SELECT would then use the third CTE instead of child_modules.

回答3:

Use like following :

Create FUNCTION [dbo].[fnGetAllData] 
(
    @UserId int    
)
RETURNS TABLE
AS
RETURN  
(
    WITH UserPRecursive AS
    (   
    SELECT u.userid,u.OverrideID
    FROM UserOverride u where OverrideID=@UserId
    UNION ALL
    SELECT C.userid,C.OverrideID
    FROM UserOverride AS C INNER JOIN UserPRecursive AS cr
    ON C.OverrideID = cr.userid 
    )   
    , UserCRecursive AS
    (   
    SELECT u.userid,u.OverrideID
    FROM UserOverride u where UserID=@UserId
    UNION ALL
    SELECT C.userid,C.OverrideID
    FROM UserOverride AS C INNER JOIN UserCRecursive AS cr
    ON C.userid = cr.OverrideID 
    )
    SELECT distinct CR1.userid as userId FROM UserPRecursive AS CR1
    UNION ALL
    SELECT distinct cr2.OverrideID as userId FROM UserCRecursive AS CR2 
    UNION ALL
    select userid from [User] where UserID=@UserId
)