Click here to Skip to main content
15,908,768 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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
Posted
Updated 5-Apr-17 8:10am
v2
Comments
ZurdoDev 4-Apr-17 7:01am    
What was the result?
Richard Deeming 4-Apr-17 8:28am    
Views cannot have parameters. As far as I can see, what you've written is equivalent to SELECT * FROM TBL_EQUIPMENT.
ABAKh 4-Apr-17 13:31pm    
Other wise I want to create view for table has (equipid,equipName,levelid)
Inserted data will be like:
(1,'a',0),(2,'b',1),(3,'c',1),(4,'d',2),(5,'e',3),...
CHill60 5-Apr-17 10:27am    
Looks like you actually want a stored procedure not a View
ABAKh 5-Apr-17 12:10pm    
No, I want view to make a report

1 solution

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[^]
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900