How to make single where condition for this SQL query?

Question

I am using MSSQL 2005 Server and I have the following SQL query.

IF @CategoryId IN (1,2,3)
    BEGIN
     INSERT INTO @search_temp_table 
     SELECT * FROM (SELECT d.DataId, (SELECT [Name] FROM Category WHERE CategoryId = d.CategoryId) AS 'Category', d.Description, d.CompanyName, d.City, d.CategoryId,
       d.CreatedOn, d.Rank, d.voteCount, d.commentCount, d.viewCount
     FROM Data d 
       INNER JOIN Keyword k
        ON d.DataId = k.DataId
     WHERE FREETEXT(k.Keyword, @SearchQ) AND d.CategoryId=@CategoryId AND d.IsSearch=1 AND d.IsApproved=1 ) AS Search_Data
    END
    ELSE
     BEGIN 
      INSERT INTO @search_temp_table 
      SELECT * FROM (SELECT d.DataId, (SELECT [Name] FROM Category WHERE CategoryId = d.CategoryId) AS 'Category', d.Description, d.CompanyName, d.City, d.CategoryId,
        d.CreatedOn, d.Rank, d.voteCount, d.commentCount, d.viewCount
      FROM Data d 
        INNER JOIN Keyword k
         ON d.DataId = k.DataId
      WHERE FREETEXT(k.Keyword, @SearchQ) AND d.IsSearch=1 AND d.IsApproved=1 ) AS Search_Data
     END

In the above query I have the category condition,

d.CategoryId=@CategoryId

which is executed when any category is passed, if no category is passed then I am not considering category condition in where clause, To implement category condition only when if the category in (1,2,3) I have used If-Clause, but can't we do this in single where query?? that means just check if the values is there in the category (or if it's easy then we can only check for 1,2,3 values) then that condition will be applied else query will not consider the category condition.

Is there any way, using CASE, or NOT NULL statements ??

Answer 1

If @CategoryId is NULL when you don't want to filter by it you can use the below condition...

ISNULL(@CategoryId, d.CategoryId) = d.CategoryId

So if it's NULL then it equals itself and wont filter

EDIT

I like Marc Miller's COALESCE example and you could use either and I really shouldn't comment on the performance of one verses the other but...

My gut tells me ISNULL should win out but have a look at some of the debates on this issue if you have nothing better to do (or if performance is REALLY critical in this query).

NOTE: If the d.CategoryId in the table can be NULL then this approach will fail and the CASE WHEN THEN approach elsewhere on this question should be used

Answer 2

Doing a ((@CategoryId IN (1,2,3) AND CategoryId=@CategoryId) OR NOT @CategoryId IN (1,2,3)) will check category id if it is 1, 2 or 3 otherwise it won't apply that filter.

    WHERE FREETEXT(k.Keyword, @SearchQ) AND ((@CategoryId IN (1,2,3) AND d.CategoryId=@CategoryId) OR NOT @CategoryId IN (1,2,3))  AND d.IsSearch=1 AND d.IsApproved=1 ) AS Search_Data

Answer 3

If category is in 1,2 or 3 then use the specified @CategoryId to filter or else don't by checking itself.

AND IsNull(d.CategoryId, 1) = case when @CategoryId in (1,2,3) then @CategoryId else IsNull(d.CategoryId, 1) end

This query also works when @Category or CategoryId is null And the "If" statement can go away.

Full query below

INSERT INTO @search_temp_table 
    SELECT * FROM (SELECT d.DataId, (SELECT [Name] FROM Category WHERE CategoryId = d.CategoryId) AS 'Category', d.Description, d.CompanyName, d.City, d.CategoryId,
                    d.CreatedOn, d.Rank, d.voteCount, d.commentCount, d.viewCount
    FROM    Data d 
            INNER JOIN Keyword k ON d.DataId = k.DataId
    WHERE   FREETEXT(k.Keyword, @SearchQ) 
      AND IsNull(d.CategoryId, 1) = case when @CategoryId in (1,2,3) then @CategoryId else IsNull(d.CategoryId, 1) end
      AND d.IsSearch=1 
      AND d.IsApproved=1 ) AS Search_Data

*WARNING: Make sure to check against execution plan whether above query is slower than using "if" statement.

Answer 4

If the only difference is your where clause then you could do this:

d.CategoryId = COALESCE(@CategoryId, d.CategoryId)

Not sure why you need the IN clause (IN (1,2,3)) as you mentioned that your reason for checking for it is to make sure it isn't NULL. So this should work the way you described.

Answer 5

Could do a LEFT JOIN in there, like this:

INSERT INTO @search_temp_table
SELECT  *
FROM    (
    SELECT d.DataId,
      c.[Name] as 'Category',
      d.Description, d.CompanyName, d.City, d.CategoryId,
      d.CreatedOn, d.Rank, d.voteCount, d.commentCount, d.viewCount
      FROM Data d 
            INNER JOIN Keyword k ON d.DataId = k.DataId
      LEFT JOIN Category c on c.CategoryId=d.CategoryId
       AND c.CategoryId=@CategoryId
      WHERE FREETEXT(k.Keyword, @SearchQ)
      AND d.IsSearch=1
      AND d.IsApproved=1
) AS Search_Data

you wouldn't need the if statement anymore either.

Also, it's very important that you have the c.CategoryId=@CategoryId within the LEFT JOIN, if you move it to the WHERE clause it will force the LEFT JOIN into an INNER JOIN.

Answer 6

Similiar to marks answer you can do the following:

WHERE FREETEXT(k.Keyword, @SearchQ) 
  AND d.IsSearch=1 
  AND d.IsApproved=1 
  AND ((@CategoryId NOT IN (1,2,3)) OR (d.CategoryId = @CategoryId))
) AS Search_Data

This way you eliminiate the function call