如何在视图中实现CTE [英] How do I implement CTE in view

问题描述

我有CTE以下，它正确执行，我想在视图中实现这个CTE，我试过但结果并不像我预期的那样。



这个是CTE：

DECLARE @levelId BIGINT;

SET @levelId = 7;

with tblChild AS

（

SELECT *

FROM TBL_EQUIPMENT WHERE levels = @levelId

UNION ALL

SELECT TBL_EQUIPMENT。* FROM TBL_EQUIPMENT加入tblChild ON TBL_EQUIPMENT.levels = tblChild.equipID

）

SELECT *

来自tblChild

选项（MAXRECURSION） 32767）



我尝试过的事情：



我创造了一个类似的视图这个：

创建视图V_AllSubEquipment

AS

with tblChild AS

（

SELECT *

FROM TBL_EQUIPMENT WHERE levels = levels

UNION ALL

SELECT TBL_EQUIPMENT。* FROM TBL_EQUIPMENT JOIN tblChild ON TBL_EQUIPMENT。 levels = tblChild.equipID

）

SELECT *

FROM tblChild

解决方案

响应OP的最后评论......



您可以在报告中使用存储过程的输出，优点是SP可以获取参数但视图不能。



这里有关于使用参数创建存储过程的说明 - 如何使用参数创建SQL Server存储过程 [ ^ ]

以下是一些您可能会觉得有用的CodeProject文章：

SQL Server存储过程概述 [ ^ ]

SQL Server存储过程与示例一起解释 [ ^ ]和

在存储中构建动态SQL程序 [ ^ ]

I have below CTE, it executes properly, i want to implement this CTE in view, i tried but the result was not as what i expected.

This is the CTE :
DECLARE @levelId BIGINT;
SET @levelId = 7;
WITH tblChild AS
(
SELECT *
FROM TBL_EQUIPMENT WHERE levels = @levelId
UNION ALL
SELECT TBL_EQUIPMENT.* FROM TBL_EQUIPMENT JOIN tblChild ON TBL_EQUIPMENT.levels = tblChild.equipID
)
SELECT *
FROM tblChild
OPTION(MAXRECURSION 32767)

What I have tried:

I CREATED VIEW LIKE THIS :
CREATE VIEW V_AllSubEquipment
AS
WITH tblChild AS
(
SELECT *
FROM TBL_EQUIPMENT WHERE levels = levels
UNION ALL
SELECT TBL_EQUIPMENT.* FROM TBL_EQUIPMENT JOIN tblChild ON TBL_EQUIPMENT.levels = tblChild.equipID
)
SELECT *
FROM tblChild

解决方案

In response to the OP's last comments...

You can use the output of a stored procedure in reports, the advantage being that SPs can take parameters but Views cannot.

There are instructions on creating a Stored Procedure with parameters here - How to create a SQL Server stored procedure with parameters[^]
And here are some CodeProject articles you may find useful:
Overview of SQL Server Stored Procedure[^]
SQL Server Stored Procedure explained with Examples[^] and
Building Dynamic SQL In a Stored Procedure[^]

这篇关于如何在视图中实现CTE的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！