Bypass last INNER JOIN in query

Question

I have the following stored procedure which takes a user ID, a starting date, an end date, and a list of codes in a comma-delimited list, and it returns all activity records between those two dates which match one of the codes in the list.

ALTER PROCEDURE [dbo].[ActivitiesSummary] 

@UserID varchar(30),
@StartDate datetime,
@EndDate datetime,
@Codes varchar(100)

AS

BEGIN

 SET NOCOUNT ON;

 SELECT act.SectionID, act.UnitID, act.ActivityCode
 FROM dbo.Activities act INNER JOIN ConvertCodeListToTbl(@Codes) i ON act.ActivityCode = i.code
 WHERE act.ActivityDate>=@Startdate AND act.ActivityDate<@EndDate
 GROUP BY act.SectionID, act.UnitID, act.ActivityCode
 ORDER BY act.SectionID, act.UnitID, act.ActivityCode
END

ConvertCodeListToTbl(@Codes) is a function that takes a comma-delimited list of codes (e.g., 'A0001, B0001, C0001') and returns a table with one code per row:

A0001
B0001
C0001

This method works really well except when no codes have been selected. When that occurs, I receive no records back because @Codes='' and the last INNER JOIN returns no records.

What I want to happen: if @Codes='', ignore the last INNER JOIN, or otherwise find a way to return all records regardless of code.

What are my options?

Answer 1

I'm not sure of a way to do this in SQL that wouldn't screw up the query plan (eg. you could do it with dynamic sql, but then you'd be producing two very different queries).

Your calling application must know that @codes is empty before it calls the stored procedure, so why not have it call a different stored procedure that doesn't do the join?

Answer 2

You could add another condition to the join statement. This probably isn't valid sql server syntax, but doing something like

... i ON (act.ActivityCode = i.code) OR IF(@codes = '', 1, 0)

would essentially join everywhere if @codes is empty.

Answer 3

In SQL server, you can set a default value for a parameter in a stored procedure.

Change your line at the top to:

@Codes varchar(100) = '*'

Then your other proc ConvertCodeListToTbl to return all values if it is passed '*' as it's parameter.

Answer 4

I don't know how the performance will compare, but this should be an otherwise identical query that does what you want:

SELECT act.SectionID, act.UnitID, act.ActivityCode 
FROM dbo.Activities act
WHERE act.ActivityDate>=@Startdate AND act.ActivityDate<@EndDate
    AND (@Codes = '' OR
         EXISTS
           (SELECT *
            FROM ConvertCodeListToTbl(@Codes)
            WHERE act.ActivityCode = code))
GROUP BY act.SectionID, act.UnitID, act.ActivityCode 
ORDER BY act.SectionID, act.UnitID, act.ActivityCode

Another option is to have ConvertCodeListToTbl return SELECT ActivityCode FROM dbo.Activities when its input is empty.

Answer 5

Is sounds like you need to change the INNER JOIN line to:

FROM dbo.Activities act INNER JOIN ConvertCodeListToTbl(@Codes) i ON (act.ActivityCode = i.code OR @Codes = '')