Stored Procedure in aspnet Database, Error 217: Limit (32) exceeded?

I'm new to stored procedures and am trying to create one for a school project. I wrote an SQL statement that does what I want and just converted it to a stored procedure (probably a bad hack job, but we're running out of time and have tons of others things to do). We're using aspnet Membership Services and 开发者_如何学Gothe entire built-in database.

What I want to do I'm using 1 table (TimesheetEntry). I want to get all the timesheets of all the employees, sum the hours per weekday to get hours/week and repeat this for the past 4 weeks (week1-week4 below) and for the past 4 months (month1-month4) below. I used nested select statements to do this.

Here is the full error message:

Error 217: Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).

Here is the full Stored Procedure:

CREATE PROCEDURE sp_GetTimesheetSummaryReport2
@dateFor DATETIME
AS
SELECT CONVERT(VARCHAR, DATEADD(DAY, -7, GETDATE()), 111) AS startDate,
    CONVERT(VARCHAR, GETDATE(), 111) AS currentDate,
    projId, wpId, empId, 
    (
        SELECT (SUM(mon) + Sum(tue) + Sum(wed) + Sum(thu) + SUM(fri) + SUM(sat) + SUM(sun)) AS Week
        FROM TimesheetEntry
        WHERE empId = t.empId AND tsDate BETWEEN @dateFor AND DATEADD(DAY, -7, @dateFor)
        GROUP BY empId, projId, wpId
    ) AS week1,
    (
        SELECT (SUM(mon) + Sum(tue) + Sum(wed) + Sum(thu) + SUM(fri) + SUM(sat) + SUM(sun)) AS Week
        FROM TimesheetEntry
        WHERE empId = t.empId AND tsDate BETWEEN DATEADD(DAY, -7, @dateFor) AND DATEADD(DAY, -14, @dateFor)
        GROUP BY empId, projId, wpId
    ) AS week2,
    (
        SELECT (SUM(mon) + Sum(tue) + Sum(wed) + Sum(thu) + SUM(fri) + SUM(sat) + SUM(sun)) AS Week
        FROM TimesheetEntry
        WHERE empId = t.empId AND tsDate BETWEEN DATEADD(DAY, -14, @dateFor) AND DATEADD(DAY, -21, @dateFor)
        GROUP BY empId, projId, wpId
    ) AS week3,
    (
        SELECT (SUM(mon) + Sum(tue) + Sum(wed) + Sum(thu) + SUM(fri) + SUM(sat) + SUM(sun)) AS Week
        FROM TimesheetEntry
        WHERE empId = t.empId AND tsDate BETWEEN DATEADD(DAY, -21, @dateFor) AND DATEADD(DAY, -28, @dateFor)
        GROUP BY empId, projId, wpId
    ) AS week4,
    (
        SELECT (SUM(mon) + Sum(tue) + Sum(wed) + Sum(thu) + SUM(fri) + SUM(sat) + SUM(sun)) AS Month
        FROM TimesheetEntry
        WHERE empId = t.empId AND MONTH(tsDate) = MONTH(@dateFor)
        GROUP BY empId, projId, wpId
    ) AS month1,
    (
        SELECT (SUM(mon) + Sum(tue) + Sum(wed) + Sum(thu) + SUM(fri) + SUM(sat) + SUM(sun)) AS Month
        FROM TimesheetEntry
        WHERE empId = t.empId AND MONTH(tsDate) = MONTH(DATEADD(MONTH, -1, @dateFor))
        GROUP BY empId, projId, wpId
    ) AS month2,
    (
        SELECT (SUM(mon) + Sum(tue) + Sum(wed) + Sum(thu) + SUM(fri) + SUM(sat) + SUM(sun)) AS Month
        FROM TimesheetEntry
        WHERE empId = t.empId AND MONTH(tsDate) = MONTH(DATEADD(MONTH, -2, @dateFor))
        GROUP BY empId, projId, wpId
    ) AS month3,
    (
        SELECT (SUM(mon) + Sum(tue) + Sum(wed) + Sum(thu) + SUM(fri) + SUM(sat) + SUM(sun)) AS Month
        FROM TimesheetEntry
        WHERE empId = t.empId AND MONTH(tsDate) = MONTH(DATEADD(MONTH, -3, @dateFor))
        GROUP BY empId, projId, wpId
    ) AS month4
FROM TimesheetEntry t
GROUP BY t.empId, t.projId, t.wpId
ORDER BY t.projId, t.wpId, t.empId;

EXECUTE sp_GetTimesheetSummaryReport '2011/02/01';

My questions

1. Why am I getting this error? Have I exceeded the stack, are there too many stored procedures/triggers/functions in the database, is my nesting too deep?

2. How can I solve it? Is my stored procedure so terrible that I should restart using a better syntax?

---

Is the call to sp_GetTimesheetSummaryReport inside sp_GetTimesheetSummaryReport2? If so, what does it do?

I think you have some infinite-loop calls going on here - procs that end up calling themselves nesting too deep.

---

Well I never would allow this on one of my databases because it has correlated subqueries which run row by row and are very slow. It would use CTEs or derived tables instead.

Is timesheet entry by chance a view that calls other views? Those can easily hit the recursion level especially if called mulitple times and you do call that timesheet at least 9 times that I can see (not including the proc that runs).

Or what does the proc you are calling do? Could it be the source of the recursion?

---

I'm still not seeing any recursion. To simplify your query, might I recommend making:

totalhours AS (mon + tue + wed + thu + fri + sat + sun) PERSISTED

into a computed column (perhaps persisted) in your table. Then you aren't either repeating the logic nor invoking an expensive user-defined function, yet getting some better maintainability.

It then gives:

    SELECT (SUM(totalhours)) AS Week
    SELECT (SUM(totalhours)) AS Month

I also noticed your query has things like:

(
    SELECT (SUM(totalhours)) AS Week
    FROM TimesheetEntry
    WHERE empId = t.empId AND tsDate BETWEEN @dateFor AND DATEADD(DAY, -7, @dateFor)
    GROUP BY empId, projId, wpId
) AS week1,

Which will return an error if there is more than one row - i.e. if an emp has more than one proj or wp. Because you are treating this as a correlated scalar subquery.