CTE and SQL Server 2005 - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

CTE and SQL Server 2005

Author

Topic

meareal
Starting Member

21 Posts

Posted - 2006-09-22 : 15:36:47

Guys, I am trying to create a recursive function using CTE. I am new at SQL Server.

Where would create this? Under Table-valued functions, Scalar-valued functions or Aggregate functions

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-09-22 : 15:39:17

CTEs are not objects like functions, they are part of the syntax of queries, so you can use them in queries in stored procedures or functions, or just in ad-hoc queries.

meareal
Starting Member

21 Posts

Posted - 2006-09-22 : 15:58:42

Thank you. Does this mean I could add a CTE inside a sproc? I am little confused as of what goes first and syntax in general...

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-09-22 : 16:09:24

Yes, just think of CTEs as an extension of the existing SELECT syntax.

nr
SQLTeam MVY

12543 Posts

Posted - 2006-09-22 : 17:02:27

see
http://www.simple-talk.com/sql/sql-server-2005/sql-server-2005-common-table-expressions/

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

meareal
Starting Member

21 Posts

Posted - 2006-09-22 : 18:47:17

Thank you for the link. I am going to look at it tonight.

meareal
Starting Member

21 Posts

Posted - 2006-09-25 : 13:28:39

Guys, I am having problems with this. This is what I have...

Table OrgChart

EmployeeID ReportsTo
304 1102
710 304
1287 304
1304 3203
3203 3445
3445 304

Example: If I want to retrieve all the employees that report to 3445, I would expect 3203 AND 1304

1304 reports directly to 3203 and 3203 reports directly to 3445

I am trying to do something like this

-- CTE Definition
;with GetEmployeesCTE (EmployeeID)
AS (
select o.employeeID
from dbo.OrgChart o
where o.reportsTo = @managerID
union all
select cte.employeeID
from GetEmployeesCTE cte
where cte.reportsTo = @managerID
)

-- Main Select statement
SELECT EmployeeID
FROM GetEmployeesCTE
OPTION (MAXRECURSION 25)

The compiler gives me this error

Msg 207, Level 16, State 1, Procedure uspGetEmployeesByManager, Line 29
Invalid column name 'reportsTo'

This is coming from where cte.reportsTo = @managerID

meareal
Starting Member

21 Posts

Posted - 2006-09-25 : 13:42:12

I found the problem guys. Thanks anyway. Here is what I was doing wrong:

;with GetEmployeesCTE (EmployeeID)
AS (
select o.employeeID
from OrgChart o
where o.reportsTo = @managerID
union all
select o.employeeID
from OrgChart o
inner join GetEmployeesCTE cte on o.reportsto = cte.employeeid
)

-- Insert statements for procedure here
SELECT EmployeeID
FROM GetEmployeesCTE
OPTION (MAXRECURSION 25)

nr
SQLTeam MVY

12543 Posts

Posted - 2006-09-25 : 13:42:43

try
;with GetEmployeesCTE (EmployeeID)
AS (
select o.employeeID
from dbo.OrgChart o
where o.reportsTo = @managerID
union all
select org.employeeID
from OrgChart org join GetEmployeesCTE cte
on org.reportsTo = cte.EmployeeID
)

-- Main Select statement
SELECT EmployeeID
FROM GetEmployeesCTE
OPTION (MAXRECURSION 25)

Oops.
Would have been first if I could type.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

meareal
Starting Member

21 Posts

Posted - 2006-09-26 : 07:10:38

Thank you "nr". I realized of my problem 2 minutes after I posted my original code snipped

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources