CODEWITHSUNDEEP
sqlsql-server-2005stored-procedures
djmzfKnm
djmzfKnm

Reputation: 27185

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

Upvotes: 2

Views: 556

Answers (6)

Rich Andrews
Rich Andrews

Reputation: 4188

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

Upvotes: 2

Ryan Abbott
Ryan Abbott

Reputation: 5417

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.

Upvotes: 1

ChrisLively
ChrisLively

Reputation: 88074

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

Upvotes: 1

dance2die
dance2die

Reputation: 36915

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.

Upvotes: 1

Mark J Miller
Mark J Miller

Reputation: 4871

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.

Upvotes: 2

Neil Bostrom
Neil Bostrom

Reputation: 2349

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

Upvotes: 1

Related Questions