Post reply

WHERE Clause

  • November 19, 2018 at 8:01 am

    #376437

    I've come across a stored procedure that has this code.


    DECLARE @status INT = -100

    SELECT * FROM Company.Schema.ClaimMain CM

    WHERE (CM.CurrentStatus = @status OR @status = -100)
    AND CM.ClaimType IN (SELECT ClaimId FROM Company.Schema.ClaimType WHERE ShowDataFlag=1)

    @status is a parameter given by the user. When I run the above code, I get a result with exactly what I asked for, where CM.CurrentStatus is equal to the parameter the user passed in. However, if -100 is chosen, all Current Statuses are returned.

    Now if I say

    DECLARE @status INT = -100

    SELECT * FROM Company.Schema.ClaimMain
    WHERE CurrentStatus= @status

    Nothing returns. No results.

    Why is it that in the first scenario I get all statuses back and in the second scenario I get no statuses back since -100 is being passed in both situations. How is SQL Server reading the -100 in the first scenario differently from -100 in the second scenario?

  • November 19, 2018 at 8:07 am

    #2013960

    NikosV - Monday, November 19, 2018 8:01 AM

    I've come across a stored procedure that has this code.


    DECLARE @status INT = -100

    SELECT * FROM Company.Schema.ClaimMain CM

    WHERE (CM.CurrentStatus = @status OR @status = -100)
    AND CM.ClaimType IN (SELECT ClaimId FROM Company.Schema.ClaimType WHERE ShowDataFlag=1)

    @status is a parameter given by the user. When I run the above code, I get a result with exactly what I asked for, where CM.CurrentStatus is equal to the parameter the user passed in. However, if -100 is chosen, all Current Statuses are returned.

    Now if I say

    DECLARE @status INT = -100

    SELECT * FROM Company.Schema.ClaimMain
    WHERE CurrentStatus= @status

    Nothing returns. No results.

    Why is it that in the first scenario I get all statuses back and in the second scenario I get no statuses back since -100 is being passed in both situations. How is SQL Server reading the -100 in the first scenario differently from -100 in the second scenario?

    It's because you have
    DECLARE @status INT = -100

    So your SQL Is executes as:
    SELECT * FROM Company.Schema.ClaimMain CM
    WHERE (CM.CurrentStatus =-100  OR -100 = -100)
    AND CM.ClaimType IN (SELECT ClaimId FROM Company.Schema.ClaimType WHERE ShowDataFlag=1)

    Which is the same as:
    SELECT *
    FROM Company.Schema.ClaimMain CM
    WHERE CM.ClaimType IN (SELECT ClaimId FROM Company.Schema.ClaimType WHERE ShowDataFlag=1)

  • November 19, 2018 at 8:17 am

    #2013964

    Brilliant. Thanks a lot.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply