when category is not null then return record accordingly categoryID. When categoryID is null then al-CodePudding

SELECT TOP (@NoofQuetion) 
    *
FROM 
    tblQuestion qs
WHERE
    qs.IsActive = 1 
    AND qs.IsDeleted = 0 
    OR (qs.CategoryID IS NULL AND qs.CategoryID = @CategoryId) 
ORDER BY 
    NEWID()

In this query, @NoofQuestion is is limit apply on record. I want when categoryId IS NULL in where condition then given record all type of categoryId. And when @CategoryId is passed as a parameter, return rows with a matching categoryId only.

For example I pass @CategoryId = 2, I get only record where

ISACTIVE = 1 AND ISDELETED = 0 AND CategoryID = 2

When I pass NULL as @CategoryId, then return all rows where

ISACTIVE = 1 AND ISDELETED = 0

Give me solution.

CodePudding user response：

You swapped the OR and AND. Your WHERE statement should look like

qs.IsActive = 1 AND qs.IsDeleted = 0 AND (qs.CategoryID IS NULL OR qs.CategoryID = @CategoryId)

EDIT:

After some testing, it turns out there's a second problem in your WHERE clause. You're checking if the CategoryId in the table is null, not if your @CategoryId variable is null. After this change and the change mentioned above, your WHERE clause should look like

qs.IsActive = 1 AND qs.IsDeleted = 0 AND (@CategoryID IS NULL OR qs.CategoryID = @CategoryId)

Here's the SQL Fiddle I used to test it.