join condition depends on the parameter

Question

Hi. I'm a sql newbie, I use mssql2005

I like to do join Action depnding on input parameter.

CREATE PROCEDURE SelectPeriodicLargeCategoryData 
    @CATEGORY_LEVEL CHAR(1),
    @CATEGORY_CODE VARCHAR(9)
AS

...


JOIN CATEGORY_AD_SYS CAS WITH(NOLOCK)
ON CA.CATEGORY_ID = [[[[[   HERE      ]]]]

above the sql.
if @CATEGORY_LEVEL = 'L' then I like to join on CAS.LCATEGORY

else if @CATEGORY_LEVEL = 'M' then I like to join on CAS.MCATEGORY

else if @CATEGORY_LEVEL = 'S' then I like to join on CAS.SCATEGORY

...

how can I do this?

Answer 1

You could use a CASE expression such as:

CASE @CATEGORY_LEVEL  
     WHEN 'L' THEN CAS.LCATEGORY
     WHEN 'M' THEN CAS.MCATEGORY
     WHEN 'S' THEN CAS.SCATEGORY
END

I'm not sure how fast that would be in a JOIN's ON condition (depends on how smart the query optimizer is about it, of course, so you'd better check by measuring with real data) -- if it turns out to have unacceptable performance, I guess you could use completely different SELECT statements depending on @CATEGORY_LEVEL as a last resort.

Answer 2

This would be easier if you normalized your tables into First Normal Form. Right now your different category columns form a repeating group.

To accomplish this normalization, you need another table to represent the many-to-many relationship between CAS and CA.

CREATE TABLE HasCategory (
  CATEGORY_ID    INTEGER,
  CAS_ID         INTEGER,
  CATEGORY_LEVEL CHAR(1), -- 'L' or 'M' or 'S'
  PRIMARY KEY (CATEGORY_ID, CAS_ID, CATEGORY_LEVEL),
  FOREIGN KEY (CATEGORY_ID) REFERENCES CATEGORIES,
  FOREIGN KEY (CAS_ID) REFERENCES CATEGORY_AD_SYS
); 

Then you can write your join conditions in a much more straightforward way:

SELECT ...
FROM CATEGORIES CA
JOIN HasCategory H ON (H.CATEGORY_ID = CA.CATEGORY_ID)
JOIN CATEGORY_AD_SYS CAS ON (H.CAS_ID = CAS.CAS_ID)
WHERE H.CATEGORY_LEVEL = @CATEGORY_LEVEL

Answer 3

If you can't fix the schema as Bill Karwin said, then use IF to switch queries.

Using a CASE statement will kill performance (I assume you gave indexes on the columns)

IF @CATEGORY_LEVEL = 'L'
    SELECT
    ...
    JOIN CATEGORY_AD_SYS CAS WITH(NOLOCK) ON CA.CATEGORY_ID = CAS.LCATEGORY

ELSE IF @CATEGORY_LEVEL = 'M'
    SELECT
    ...
    JOIN CATEGORY_AD_SYS CAS WITH(NOLOCK) ON CA.CATEGORY_ID = CAS.MCATEGORY 

ELSE IF @CATEGORY_LEVEL = 'M'
    SELECT
    ...
    JOIN CATEGORY_AD_SYS CAS WITH(NOLOCK) ON CA.CATEGORY_ID = CAS.SCATEGORY