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?

I managed to avoid using dynamic SQL previously because of all the security issues associated with it, so I'd like to continue to do so if possible. — ofm, Sep 30 '10 at 13:36

score 1 · Accepted Answer · edited Jan 03 '13 at 14:52

1

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 = '')

edited Jan 03 '13 at 14:52

András Ottó

7,605
1
28
38

answered Sep 30 '10 at 13:39

Mike Cheel

12,626
10
72
101

I had devised an if statement with a bit flag that worked but this is even better. – ofm Sep 30 '10 at 14:12

score 0 · Answer 2 · answered Sep 30 '10 at 12:59

0

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?

answered Sep 30 '10 at 12:59

Andrew Carmichael

3,086
1
22
21

If I can't find a better solution then I might have to do that, but that's not the ideal solution since this particular inner join is part of several other queries and it would mean I'd have several near-duplicate stored procedures. – ofm Sep 30 '10 at 13:28

score 0 · Answer 3 · answered Sep 30 '10 at 12:59

0

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.

answered Sep 30 '10 at 12:59

Marc B

356,200
43
426
500

The closest thing I can think of in T-SQL is a CASE..WHEN statement, which as far as I know would require another SELECT statement and get rather messy. – ofm Sep 30 '10 at 13:32

score 0 · Answer 4 · answered Sep 30 '10 at 13:02

0

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.

answered Sep 30 '10 at 13:02

JNK

63,321
15
122
138

Unfortunately, ConvertCodeListToTbl only sees codes passed through @Codes, so it can't return the whole list. – ofm Sep 30 '10 at 13:23
@ofm - Is it not possible to add a line to the proc along the lines of `IF @Codes = '*' THEN SELECT * FROM CODES...ELSE....` – JNK Sep 30 '10 at 14:07
It might be possible, although the answer I chose more concise. – ofm Oct 04 '10 at 12:04

score 0 · Answer 5 · answered Sep 30 '10 at 13:38

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.

Bypass last INNER JOIN in query

5 Answers5

Linked