CODEWITHSUNDEEP
sqlsql-servert-sql
SBB
SBB

Reputation: 8970

TSQL Nested IF logic

I have a query that I am using to pull data to my page. Currently, you can select a "Location" on my report and show data for that location. I want to add a "Global" option so when that is selected, the query will return ALL data.

This is my current working SP when querying the data by location:

IF (@action = 'segment')

    BEGIN
        SELECT   B.[segmentName],
                 count(A.[segmentID]) AS total,
                 A.[meetingID],
                 C.[center]
        FROM     Focus_Meetings_Segments AS A
                 INNER JOIN
                 Focus_Segments AS B
                 ON A.[segmentID] = B.[id]
                 JOIN Focus_Meetings as C
                 ON C.[id] = A.[meetingID]
        WHERE C.[center] = @location
        GROUP BY B.[segmentName], A.[meetingID], C.[center]
        ORDER BY total DESC
        FOR      XML PATH ('segment'), TYPE, ELEMENTS, ROOT ('root');
    END
ELSE

Her is how the query would be for ALL data:

IF (@action = 'segment')

    BEGIN
        SELECT   B.[segmentName],
                 count(A.[segmentID]) AS total
        FROM     Focus_Meetings_Segments AS A
                 INNER JOIN
                 Focus_Segments AS B
                 ON A.[segmentID] = B.[id]
        GROUP BY B.[segmentName], A.[segmentID]
        ORDER BY total DESC
        FOR      XML PATH ('segment'), TYPE, ELEMENTS, ROOT ('root');
    END
ELSE

Is there a way do do this inline with case logic or possible to do a nested if statement like below?

IF (@action = 'segment')
    IF (@location = 'global')

Upvotes: 0

Views: 97

Answers (1)

Charles Bretana
Charles Bretana

Reputation: 146499

You don't need two different Statements. Just change the Where clause so that it is conditional based on the value of @location

 Where C.[center] = coalesce(@location, C.[center])

and then just pass a null when you want them all.

NOTE: If join to B can fail sometimes, make it an Outer join.

    SELECT B.[segmentName],
       count(A.[segmentID]) AS total,
        A.[meetingID],
         C.[center]
    FROM  Focus_Meetings_Segments AS A
       JOIN Focus_Segments B
          ON A.[segmentID] = B.[id]
       Left JOIN Focus_Meetings as C
          ON C.[id] = A.[meetingID]
             And C.[center] = coalesce(@location, C.[center])
    GROUP BY B.[segmentName], A.[meetingID], C.[center]
    ORDER BY total DESC
    FOR  XML PATH ('segment'), TYPE, ELEMENTS, ROOT ('root');

Upvotes: 1

Related Questions