How to make single where condition for this SQL query?

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 ??

From stackoverflow

• 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

  Sung Meister : This doesn't check whether @Category is in 1,2 or 3.

  Rich Andrews : Hmm - they way I read the question was that the values passed in could only be 1,2 or 3 and that was why they were in the IF statement. Either way it's a bloody good way of implementing optional filtering :)

  Tom H. : This may also fail if the CategoryId in the table can be NULL

  Rich Andrews : Good point - I've generally used this approach on IDENTITY columns so it's not been a problem for me in the past but I'll edit the post to point this out.

• 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
  

• 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.

  Tom H. : This may not return the correct data if the CategoryId in the table can be NULL

  Sung Meister : It should work now even though CategoryId is null

• 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.

• 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.

• 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