Complicated Sql Server Query

I am trying to write an SQL (Server) query which will return all events on a current day, and for all events where the column recurring= 1, I want it to return this event on the da

Solution 1:

The simplest solution would be to alter the following 2 lines

AND    e.event_start >= @4
AND    e.event_end <= @5"

to

AND    DATEADD(WEEK, w.Weeks, e.event_start) >= @4
AND    DATEADD(WEEK, w.Weeks, e.event_end) <= @5"

However, I'd advise putting all this SQL into a stored procedure, SQL-Server will cache the execution plans and it will result in (slightly) better performance.

CREATEPROCEDURE dbo.GetEvents @UserNameVARCHAR(50), @StartDate DATETIME, @EndDate DATETIME
ASBEGIN-- DEFINE A CTE TO GET ALL GROUPS ASSOCIATED WITH THE CURRENT USER
;WITHGroupsAS 
(   SELECT  GroupID 
    FROM    Membership  m
            INNERJOIN Users u
                ON m.UserID = u.UserID
    WHERE   Username =@UserNameGROUPBY GroupID
),
-- DEFINE A CTE TO GET ALL EVENTS FOR THE GROUPS DEFINED ABOVE
AllEvents AS
(   SELECT  e.*FROM    event e
            INNERJOINGroups m 
                ON m.GroupID = e.group_id
    UNIONALLSELECT  e.event_id, e.title, e.description, DATEADD(WEEK, w.weeks, e.event_start), DATEADD(WEEK, w.weeks, e.event_end), e.group_id, e.recurring
    FROM    event e
            INNERJOINGroups m 
                ON m.GroupID = e.group_id
            CROSSJOIN 
            (   SELECTROW_NUMBER() OVER (ORDERBY Object_ID) AS weeks
                FROM    SYS.OBJECTS
            ) AS w
    WHERE  e.recurring =1
)   
-- GET ALL EVENTS WHERE THE EVENTS FALL IN THE PERIOD DEFINEDSELECT*FROM    AllEvents
WHERE   Event_Start >=@StartDateAND     Event_End <=@EndDateEND

Then you can call this with

var result = db.Query("EXEC dbo.GetEvents @0, @1, @2", username, start, end);

This elimates the need to iterate over groups in your code behind. If this is actually a requirement then you could modify the stored procedure to take @GroupID as a parameter, and change the select statements/where clauses as necessary.

I have assumed knowledge of Common Table Expressions. They are not required to make the query work, they just make things slightly more legible in my opinion. I can rewrite this without them if required.

Solution 2:

I would check my parameters one at a time against some trivial SQL, just to rule them out as possible culprits. Something like this:

var result= db.Query("select r=cast(@0 as varchar(80))",username);
var result= db.Query("select r=cast(@0 as int)",newGroupID);
var result= db.Query("select r=cast(@0 as datetime)",start);
var result= db.Query("select r=cast(@0 as datetime)",end);