如何在SQL中比较smallint -1

I'm trying to find out if status_id field value of SQL smallint is -1 and and get the records that doesn't have -1 for that field. My stored proc content is as follows:

我试图找出SQL smallint的status_id字段值是否为-1，并获取该字段没有-1的记录。我存储的proc内容如下：

SELECT DISTINCT TOP 7
        cs.case_id,
        cs.status_id,
        cm.company_name, 
        cs.created_time,
        cs.severity_id,
        cs.last_updated_time,
        COALESCE(NULLIF(cs.priority,''), 'Medium') AS case_priority
    FROM 
        tblcase cs with (nolock)
        INNER JOIN tblcompany cm with (nolock) ON (cs.company_id=cm.company_id)

    WHERE 
        CONVERT(INT, cs.status_id) <> -1  AND
        (cs.cas_case_owner = @userId AND cs.is_notify_co = 1) OR
        (cs.activity_owner = @userId AND cs.is_notify_ao = 1)

    ORDER BY cs.severity_id DESC, cs.case_id ASC

I have tried CONVERT(int, cs.status_id) <> -1), cs.status_id <> CONVERT(smallint, -1), cs.status_id != CAST('-1' AS smallint) and more, but I still keep getting records with -1 as the status_id. Please help me understand what I'm doing wrong before downvoting.

我试过CONVERT（int，cs.status_id）<> -1），cs.status_id <> CONVERT（smallint，-1），cs.status_id！= CAST（' - 1'AS smallint）等等，但我仍然继续获取带有-1的记录作为status_id。在downvoting之前，请帮助我理解我做错了什么。

3 个解决方案

#1

I think you forgot to use brackets for OR operation

我想你忘了使用括号进行OR运算

cs.status_id <> -1 AND
     ( -- open
        (cs.cas_case_owner = @userId AND cs.is_notify_co = 1) 
        OR
        (cs.activity_owner = @userId AND cs.is_notify_ao = 1)
     ) -- close

See my another answer about it here - SQL Server Left Join Counting

在这里查看我的另一个答案 - SQL Server Left Join Counting

#2

Try this

尝试这个

SELECT DISTINCT TOP 7
        cs.case_id,
        cs.status_id,
        cm.company_name, 
        cs.created_time,
        cs.severity_id,
        cs.last_updated_time,
        COALESCE(NULLIF(cs.priority,''), 'Medium') AS case_priority
    FROM 
        tblcase cs with (nolock)
        INNER JOIN tblcompany cm with (nolock) ON (cs.company_id=cm.company_id)

    WHERE ISNULL(CAST(cs.status_id AS INT),0)<> -1 
        AND cs.is_notify_co = 1
        AND
        (
            cs.cas_case_owner = @userId 
            OR
            cs.activity_owner = @userId 
        )
    ORDER BY cs.severity_id DESC, cs.case_id ASC

#3

Try this:

尝试这个：

SELECT DISTINCT TOP 7
        cs.case_id,
        cs.status_id,
        cm.company_name, 
        cs.created_time,
        cs.severity_id,
        cs.last_updated_time,
        COALESCE(NULLIF(cs.priority,''), 'Medium') AS case_priority
    FROM 
        tblcase cs with (nolock)
        INNER JOIN tblcompany cm with (nolock) ON (cs.company_id=cm.company_id)

        WHERE 
            (
              ( cs.status_id <> -1  AND cs.cas_case_owner = @userId) AND 
              ( (cs.is_notify_co = 1) OR (cs.is_notify_ao = 1))
            )

    ORDER BY cs.severity_id DESC, cs.case_id ASC

#1