Stored Procedure not returning filtered results when using NULL to ignore parameter if empty

Question

I have a query in which the user can choose which columns they wish to search on (each column has a corresponding filter on the web). I've used the NULL method to try and ignore the parameter if it is passed into the DB as NULL. Having all fields NULL works as expected and returns all records, but when attempting to filter the information the results are the same and the query returns everything.

I can't seem to find out why this might be happening, it could be something really small and obvious but I just can't see it.

ALTER PROCEDURE [dbo].[GetChatListFilter] 
    @SiteKey int,
    @invited int = NULL,
    @starttime varchar(15),
    @finishtime varchar(15) = NULL,
    @visitor varchar(50) = NULL,
    @wait int = NULL,
    @operators varchar(max) = NULL,
    @department varchar(max) = NULL,
    @skills varchar(max) = NULL,
    @chattime int = NULL,
    @rating int = NULL,
    @email varchar(max) = NULL,
    @message varchar(max) = NULL,
    @dns varchar(max) = NULL,
    @visit varchar(max) = NULL,
    @city varchar(max) = NULL,
    @country varchar(max) = NULL
AS

    SELECT        
        UserChats.Invited, 
        UserChats.StartTime AS Start, 
        UserChats.FinishTime AS Finish, 
        UserChats.VisitorName As Visitor, 
        UserChats.WaitedForSecs AS Wait, 
        UserChats.TakenByUser AS Operator, 
        UserChats.TakenByDept AS [Dept.], 
        dbo.SkillIDsToName(UserChats.SkillIDList) AS Skill,
        UserChats.ChattedForSecs AS [Time], 
        UserChats.Rating AS Rate, 
        UserChats.MessageEmail AS Email, 
        UserChats.MessageText AS [Message], 
        UserChats.VisitorIP AS DNS, 
        UserChats.VisitorVisitNumber AS Visit, 
        VisitDetail.City, 
        VisitDetail.Country
    FROM             
        UserChats 
    INNER JOIN
        VisitDetail ON  UserChats.VisitID =  VisitDetail.VisitID
    WHERE 
        UserChats.SiteKey = @SiteKey AND
        UserChats.Invited = @invited OR @invited is NULL AND
        UserChats.StartTime = @starttime OR @starttime = '01/01/1900' AND
        UserChats.FinishTime = @finishtime OR @finishtime = '01/01/1900' AND
        UserChats.VisitorName = @visitor OR @visitor is NULL AND
        UserChats.WaitedForSecs = @wait OR @wait is NULL AND
        UserChats.TakenByUser = @operators OR @operators is NULL AND
        UserChats.TakenByDept = @department OR @department is NULL AND
        dbo.SkillIDsToName(UserChats.SkillIDList) = @skills OR @skills is NULL AND
        UserChats.ChattedForSecs = @chattime OR @chattime is NULL AND
        UserChats.Rating = @rating OR @rating is NULL AND
        UserChats.MessageEmail = @email OR @email is NULL AND
        UserChats.MessageText = @message OR @message is NULL AND
        UserChats.VisitorIP = @dns OR @dns is NULL AND
        UserChats.VisitorVisitNumber = @visit OR @visit is NULL AND
        VisitDetail.City = @city OR @city is NULL AND
        VisitDetail.Country = @country or @country is NULL

Answer 1

As I told above, I perhaps would go for dynamic code here, since your query contains a lot of OR statements. I've rewritten your procedure like that. Please leave a comment or ideas.

What I can think of all records not comming back - you hardcoded @starttime and @finishtime, just deal with them. I tried to do it in this code, but it's quite hard when there's no sample data.

ALTER PROCEDURE [dbo].[GetChatListFilter]
(
    @SiteKey INT
    , @invited INT = NULL
    , @starttime VARCHAR(15)
    , @finishtime VARCHAR(15) = NULL
    , @visitor VARCHAR(50) = NULL
    , @wait INT = NULL
    , @operators VARCHAR(MAX) = NULL
    , @department VARCHAR(MAX) = NULL
    , @skills VARCHAR(MAX) = NULL
    , @chattime INT = NULL
    , @rating INT = NULL
    , @email VARCHAR(MAX) = NULL
    , @message VARCHAR(MAX) = NULL
    , @dns VARCHAR(MAX) = NULL
    , @visit VARCHAR(MAX) = NULL
    , @city VARCHAR(MAX) = NULL
    , @country VARCHAR(MAX) = NULL
)
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRY
        DECLARE @SQL NVARCHAR(MAX)
            , @SQLParams NVARCHAR(MAX);

        SET @SQL = N'
            SELECT UC.Invited
                , UC.StartTime AS Start
                , UC.FinishTime AS Finish
                , UC.VisitorName AS Visitor
                , UC.WaitedForSecs AS Wait
                , UC.TakenByUser AS Operator
                , UC.TakenByDept AS [Dept.]
                , dbo.SkillIDsToName(UC.SkillIDList) AS Skill
                , UC.ChattedForSecs AS [Time]
                , UC.Rating AS Rate
                , UC.MessageEmail AS Email
                , UC.MessageText AS [Message]
                , UC.VisitorIP AS DNS
                , UC.VisitorVisitNumber AS Visit
                , VD.City
                , VD.Country
            FROM dbo.UserChats AS UC
            INNER JOIN dbo.VisitDetail AS VD
                ON UC.VisitID = VD.VisitID
            WHERE UC.SiteKey = @p0';

        IF NULLIF(@invited, '') IS NOT NULL                 SET @SQL += N' AND UC.Invited = @p1';
        IF NULLIF(@starttime, '01/01/1900') IS NOT NULL     SET @SQL += N' AND UC.StartTime = @p2';
        IF NULLIF(@finishtime, '01/01/1900') IS NOT NULL    SET @SQL += N' AND UC.FinishTime = @p3';
        IF NULLIF(@visitor, '') IS NOT NULL                 SET @SQL += N' AND UC.VisitorName = @p4';
        IF NULLIF(@wait, '') IS NOT NULL                    SET @SQL += N' AND UC.WaitedForSecs = @p5';
        IF NULLIF(@operators, '') IS NOT NULL               SET @SQL += N' AND UC.TakenByUser = @p6';
        IF NULLIF(@department, '') IS NOT NULL              SET @SQL += N' AND UC.TakenByDept = @p7';
        IF NULLIF(@skills, '') IS NOT NULL                  SET @SQL += N' AND dbo.SkillIDsToName = @p8';
        IF NULLIF(@chattime, '') IS NOT NULL                SET @SQL += N' AND UC.ChattedForSecs = @p9';
        IF NULLIF(@rating, '') IS NOT NULL                  SET @SQL += N' AND UC.Rating = @p10';
        IF NULLIF(@email, '') IS NOT NULL                   SET @SQL += N' AND UC.MessageEmail = @p11';
        IF NULLIF(@message, '') IS NOT NULL                 SET @SQL += N' AND UC.MessageText = @p12';
        IF NULLIF(@dns, '') IS NOT NULL                     SET @SQL += N' AND UC.VisitorIP = @p13';
        IF NULLIF(@visit, '') IS NOT NULL                   SET @SQL += N' AND UC.VisitorVisitNumber @p14';
        IF NULLIF(@city, '') IS NOT NULL                    SET @SQL += N' AND VD.City = @p15';
        IF NULLIF(@country, '') IS NOT NULL                 SET @SQL += N' AND VD.Country = @p16';

        SET @SQLParams = N'
              @p0 INT
            , @p1 INT
            , @p2 VARCHAR(15)
            , @p3 VARCHAR(15)
            , @p4 VARCHAR(50)
            , @p5 INT
            , @p6 VARCHAR(MAX)
            , @p7 VARCHAR(MAX)
            , @p8 VARCHAR(MAX)
            , @p9 INT
            , @p10 INT
            , @p11 VARCHAR(MAX)
            , @p12 VARCHAR(MAX)
            , @p13 VARCHAR(MAX)
            , @p14 VARCHAR(MAX)
            , @p15 VARCHAR(MAX)
            , @p16 VARCHAR(MAX)';

        EXECUTE sp_executesql @SQL
            , @SQLParams
            , @p0 = @SiteKey
            , @p1 = @invited
            , @p2 = @starttime
            , @p3 = @finishtime
            , @p4 = @visitor
            , @p5 = @wait
            , @p6 = @operators
            , @p7 = @department
            , @p8 = @skills
            , @p9 = @chattime
            , @p10 = @rating
            , @p11 = @email
            , @p12 = @message
            , @p13 = @dns
            , @p14 = @visit
            , @p15 = @city
            , @p16 = @country;
    END TRY
    BEGIN CATCH
        SELECT ERROR_MESSAGE();
    END CATCH
END

Answer 2

You can use case statements in the following manner:

    ALTER PROCEDURE [dbo].[GetChatListFilter] 
        @SiteKey int,
        @invited int = NULL,
        @starttime varchar(15),
        @finishtime varchar(15) = NULL,
        @visitor varchar(50) = NULL,
        @wait int = NULL,
        @operators varchar(max) = NULL,
        @department varchar(max) = NULL,
        @skills varchar(max) = NULL,
        @chattime int = NULL,
        @rating int = NULL,
        @email varchar(max) = NULL,
        @message varchar(max) = NULL,
        @dns varchar(max) = NULL,
        @visit varchar(max) = NULL,
        @city varchar(max) = NULL,
        @country varchar(max) = NULL
    AS

        SELECT        
            UserChats.Invited, 
            UserChats.StartTime AS Start, 
            UserChats.FinishTime AS Finish, 
            UserChats.VisitorName As Visitor, 
            UserChats.WaitedForSecs AS Wait, 
            UserChats.TakenByUser AS Operator, 
            UserChats.TakenByDept AS [Dept.], 
            dbo.SkillIDsToName(UserChats.SkillIDList) AS Skill,
            UserChats.ChattedForSecs AS [Time], 
            UserChats.Rating AS Rate, 
            UserChats.MessageEmail AS Email, 
            UserChats.MessageText AS [Message], 
            UserChats.VisitorIP AS DNS, 
            UserChats.VisitorVisitNumber AS Visit, 
            VisitDetail.City, 
            VisitDetail.Country
        FROM             
            UserChats 
        INNER JOIN
            VisitDetail ON  UserChats.VisitID =  VisitDetail.VisitID
        WHERE 
            UserChats.SiteKey = @SiteKey AND
            UserChats.Invited = CASE WHEN @invited IS NOT NULL THEN @invited ELSE UserChats.Invited END
    AND     UserChats.StartTime = CASE WHEN @starttime <> '01/01/1900' THEN @starttime ELSE UserChats.StartTime END
    AND     UserChats.FinishTime = CASE WHEN @finishtime <> '01/01/1900' THEN @finishtime ELSE UserChats.FinishTime END
    AND     UserChats.VisitorName = CASE WHEN @visitor IS NOT NULL THEN @visitor ELSE UserChats.VisitorName END
    AND     UserChats.WaitedForSecs = CASE WHEN @wait IS NOT NULL THEN @wait ELSE UserChats.WaitedForSecs END
    AND     UserChats.TakenByUser = CASE WHEN @operators IS NOT NULL THEN @operators ELSE UserChats.TakenByUser END
    AND     UserChats.TakenByDept = CASE WHEN @department IS NOT NULL THEN @department ELSE UserChats.TakenByDept END
    AND     dbo.SkillIDsToName(UserChats.SkillIDList) = CASE WHEN @skills IS NOT NULL THEN @skills ELSE dbo.SkillIDsToName(UserChats.SkillIDList) END
    AND     UserChats.ChattedForSecs = CASE WHEN @chattime IS NOT NULL THEN @chattime ELSE UserChats.ChattedForSecs END
    AND     UserChats.Rating = CASE WHEN @rating IS NOT NULL THEN @rating ELSE UserChats.Rating END
    AND     UserChats.MessageEmail = CASE WHEN @email IS NOT NULL THEN @email ELSE UserChats.MessageEmail END
    AND     UserChats.MessageText = CASE WHEN @message IS NOT NULL THEN @message ELSE UserChats.MessageText END
    AND     UserChats.VisitorIP = CASE WHEN @dns IS NOT NULL THEN @dns ELSE UserChats.VisitorIP END
    AND     UserChats.VisitorVisitNumber = CASE WHEN @visit IS NOT NULL THEN @visit ELSE UserChats.VisitorVisitNumber END
    AND     VisitDetail.City = CASE WHEN @city IS NOT NULL THEN @city ELSE UserChats.City END
    AND     VisitDetail.Country = CASE WHEN @country IS NOT NULL THEN @country ELSE UserChats.Country END

Answer 3

Gordon Linoff's answer is correct, but you'll need to set each parameter to check the default, for e.g. @starttime = '', as it's not set to null as default.

Also consider adding OPTION (RECOMPILE) This should increase performance if it's available to you (SQL Server 2005 and above I think)

Answer 4

You need parentheses:

WHERE UserChats.SiteKey = @SiteKey AND
      (UserChats.Invited = @invited OR @invited is NULL) AND
      (UserChats.StartTime = @starttime OR @starttime = '1900-01-01') AND
      (UserChats.FinishTime = @finishtime OR @finishtime = '1900-01-01') AND
      . . .