TSQL Select Statement using Case or Join

Question

I am a little stuck on a situation that I have been trying to fight through. I have a page that allows a user to select all the filter options they want to search by and then it runs the query on that data.

Every field requires something to be picked but on a new field I am introducing, it's going to be optional.

It allows you to provide a list of supervisors and it will then provide all records where the agents supervisor is in the list provided; pretty straight forward. However, I am trying to make this optional as I don't want to always search by users. If I don't provide a name in the UI to pass to the stored procedure, then I want to ignore this part of the statement and get me everything regardless of the manager.

Here is the query I am working with:

SELECT a.[escID],
           a.[escReasonID],
           b.[ArchibusLocationName],
           c.[ArchibusLocationName],
           b.[DepartmentDesc],
           c.[DepartmentDesc],
           a.[escCreatedBy],
           a.[escWorkedBy],
           a.[escNotes],
           a.[preventable],
           a.[escalationCreated],
           a.[escalationTracked],
           a.[feedbackID],
           typ.[EscalationType],
           typ.[EscalationTypeText] AS escalationType,
           d.reasonText AS reasonText
    FROM   [red].[dbo].[TFS_Escalations] AS a
           LEFT OUTER JOIN
           red.dbo.EmployeeTable AS b
           ON a.escCreatedBy = b.QID
           LEFT OUTER JOIN
           red.dbo.EmployeeTable AS c
           ON a.escWorkedBy = c.QID
           LEFT OUTER JOIN
           red.dbo.TFS_Escalation_Reasons AS d
           ON a.escReasonID = d.ReasonID
           INNER JOIN
           dbo.TFS_EscalationTypes AS typ
           ON d.escType = typ.EscalationType
    WHERE  B.[ArchibusLocationName] IN (SELECT location
                                        FROM   @tmLocations)
           AND C.[ArchibusLocationName] IN (SELECT location
                                            FROM   @subLocations)
           AND B.[DepartmentDesc] IN (SELECT department
                                      FROM   @tmDepartments)
           AND C.[DepartmentDesc] IN (SELECT department
                                      FROM   @subDepartments)
           AND DATEDIFF(second, '19700101', CAST (CONVERT (DATETIME, A.[escalationCreated], 121) AS INT)) >= @startDate
           AND DATEDIFF(second, '19700101', CAST (CONVERT (DATETIME, A.[escalationCreated], 121) AS INT)) <= @endDate
           AND a.[PREVENTABLE] IN (SELECT PREVENTABLE FROM @preventable)
           AND b.MgrQID IN (SELECT leaderQID FROM @sourceLeaders)

The part that I am trying to make option is the very last line of the query:

AND b.MgrQID IN (SELECT leaderQID FROM @sourceLeaders)

Essentially, if there is no data in the temp table @sourceLeaders then it should ignore that piece of the query.

In all of the other instances of the WHERE clause, something is always required for those fields which is why that all works fine. I just cant figure out the best way to make this piece optional depending on if the temp table has data in it (the temp table is populated by the names entered in the UI that a user COULD search by).

I think Nick's answer makes sense, but it seems like it would be better to fully populate the @sourceLeaders table parameter if you expect all to be selected. — K Richard, Apr 21 '15 at 22:55
That is what I had done on other projects but this is picking supervisors from our employee table with over 65,000 records... Didn't know what the better solution would have been. — SBB, Apr 21 '15 at 22:56
Yeah, Nick's answer is easier. The only other path I am thinking would be to apply the other parameters (locations, departments, etc) and reduce the results from the employee table. — K Richard, Apr 21 '15 at 23:15

Nick.Mc · Answer 1 · 2015-04-21T22:57:05.447

3

So this line should be TRUE if something matches data in the table variable OR there is nothing in the table variable

AND 
(
   b.MgrQID IN (SELECT leaderQID FROM @sourceLeaders)
   OR
   NOT EXISTS (SELECT 1 FROM @sourceLeaders)
)

edited Apr 21 '15 at 22:57

answered Apr 21 '15 at 22:50

Nick.Mc

18,304
6
61
91

Is this missing some parenthesis somewhere? It's saying the syntax is wrong – SBB Apr 21 '15 at 22:53
While this did allow me to execute the query, it ends up timing out. It's either getting stuck somewhere or it impacts the performance substantially. – SBB Apr 21 '15 at 23:17
Yes I'm sure it impacts the performance. You might be able to improve it by using joins instead of IN... but regardless take a look at this, which has a great discussion an good links to info about this design pattern: http://stackoverflow.com/questions/3415582/how-can-i-use-optional-parameters-in-a-t-sql-stored-procedure. This optional parameter performance issue is well known and well discussed. – Nick.Mc Apr 21 '15 at 23:25
1

Don't forget to follow the links to Erlands blog which has a lot of info – Nick.Mc Apr 21 '15 at 23:26
re: optional params - there's even a lengthier discussion comparing 3 most commonly used techniques (local vars, recompile and optimize for unknown): http://dba.stackexchange.com/questions/33698/parameter-sniffing-vs-variables-vs-recompile-vs-optimize-for-unknown – YS. Apr 21 '15 at 23:33
Thanks. Read it.. understood some of it... upvoted it. This is what the internet is for!! – Nick.Mc Apr 22 '15 at 00:16

score 0 · Answer 2 · answered Apr 22 '15 at 01:28

0

Similar to Nick.McDermaid's, but uses a case statement instead :

AND 
(
   1 = CASE WHEN NOT EXISTS(SELECT 1 FROM @sourceLeaders) THEN  1
            WHEN b.MgrQID IN (SELECT leaderQID FROM @sourceLeaders) THEN 1
            ELSE 0 
       END 
)

answered Apr 22 '15 at 01:28

Moe Sisko

11,665
8
50
80

I tried this and it works great when there IS a name in the `@sourceLeaders` table but when I don't search by a specific person, I get no results for any of my criteria. – SBB Apr 29 '15 at 18:57
that's strange. As a test, I suggest moving the CASE statement from the WHERE part, to the SELECT part of the query. Then you can see what is being returned by the CASE function. e.g. SELECT a, b, CASE ... END as CaseResult, d FROM ... – Moe Sisko Apr 29 '15 at 23:55

score 0 · Answer 3 · answered Apr 22 '15 at 11:52

Maybe at the top so you have a single check

DECLARE @EmptySourceLeaders CHAR(1)
IF EXISTS (SELECT 1 FROM @sourceLeaders)
   SET @EmptySourceLeaders = 'N'
ELSE 
   SET @EmptySourceLeaders = 'Y'

Then in the joins

LEFT OUTER JOIN @SourceLeaders SL
ON b.MgrQID = SL.leaderQID

Then in the WHERE

AND  (@EmptySourceLeaders = 'Y' OR SL.leaderQID IS NOT NULL)

lots of ways to do it.

TSQL Select Statement using Case or Join

3 Answers3