CTE, I have a dream...

29 Jun 2006 9:44 PM

guercheLE

If they (CTE) were parameterized, I would stop working user functions
for use in only one place.

/*
* CTE, I have a dream...
*/
With TopRatedEmployees
(
@DepartmentId int
)
RETURNS TABLE
AS
(
SELECT TOP 10 EmployeeId,
               EmployeeName,
               Rank
FROM Employees
WHERE DepartmentId = @DepartmentId
ORDER BY Rank DESC
)
With EmployeesCount
(
@DepartmentId int
)
RETURNS int
AS
(
SELECT COUNT(*)
FROM Employees
WHERE DepartmentId = @DepartmentId
)
SELECT Departments.Id,
       Departments.Name,
       dbo.EmployeesCount(Department.Id),
       TopRatedEmployees1.EmployeeId,
       TopRatedEmployees1.EmployeeName,
FROM Departments
CROSS APPLY
     dbo.TopRatedEmployees(Department.Id) AS TopRatedEmployees1
ORDER BY Departments.Name;
GO

30 Jun 2006 6:13 AM

David Portas

<guerch***@gmail.com> wrote in message
Show quote

news:1151617460.266574.183380@j72g2000cwa.googlegroups.com...
> If they (CTE) were parameterized, I would stop working user functions
> for use in only one place.
>
> /*
> * CTE, I have a dream...
> */
> With TopRatedEmployees
> (
> @DepartmentId int
> )
> RETURNS TABLE
> AS
> (
> SELECT TOP 10 EmployeeId,
>               EmployeeName,
>               Rank
> FROM Employees
> WHERE DepartmentId = @DepartmentId
> ORDER BY Rank DESC
> )
> With EmployeesCount
> (
> @DepartmentId int
> )
> RETURNS int
> AS
> (
> SELECT COUNT(*)
> FROM Employees
> WHERE DepartmentId = @DepartmentId
> )
> SELECT Departments.Id,
>       Departments.Name,
>       dbo.EmployeesCount(Department.Id),
>       TopRatedEmployees1.EmployeeId,
>       TopRatedEmployees1.EmployeeName,
> FROM Departments
> CROSS APPLY
>     dbo.TopRatedEmployees(Department.Id) AS TopRatedEmployees1
> ORDER BY Departments.Name;
> GO
>

Assuming I've understood your pseudo-code correctly you can do it like this:

SELECT D.DepartmentId,
D.Name,
D.DepartmentId,
TopRatedEmployees.EmployeeId,
TopRatedEmployees.EmployeeName
FROM Departments AS D
CROSS APPLY
(
SELECT TOP 10 EmployeeId,
EmployeeName,
Rank
FROM Employees
WHERE DepartmentId = D.DepartmentId
ORDER BY Rank DESC
) AS TopRatedEmployees
CROSS APPLY
(
SELECT D.DepartmentId, COUNT(*) AS cnt
FROM Employees
WHERE DepartmentId = D.DepartmentId
) AS EmployeesCount
ORDER BY D.Name;

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--