使用 NULL 忽略参数(如果为空)时，存储过程不返回过滤结果 [英] Stored Procedure not returning filtered results when using NULL to ignore parameter if empty

问题描述

我有一个查询，用户可以在其中选择他们希望搜索的列(每列在网络上都有一个相应的过滤器).如果参数作为 NULL 传递到数据库中，我已经使用 NULL 方法尝试忽略该参数.将所有字段设为 NULL 会按预期工作并返回所有记录，但在尝试过滤信息时，结果是相同的，并且查询会返回所有内容.

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

推荐答案

正如我上面所说的，我可能会在这里使用动态代码，因为您的查询包含很多 OR 语句.我已经像这样重写了你的程序.请留下评论或想法.

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.

我能想到的所有记录都没有回来 - 你硬编码了 @starttime 和 @finishtime，只需处理它们.我尝试在这段代码中做到这一点，但是在没有示例数据的情况下很难.

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

这篇关于使用 NULL 忽略参数(如果为空)时，存储过程不返回过滤结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！