A Simple T-SQL question Regarding Nulls and Selects

  • I have a stored procedure that allows for nulls. When selecting data from my tables I normally do something like

    Create Procedure DoSomething(@Status Char(1) = Null)

    if (@Status Is Null)

    Begin

    Select Key, Description, Status

    From Table

    Order By Description

    End

    Else

    Begin

    Select Key, Description, Status

    From Table

    Where Status = @status

    Order By Description

    End

    I know there my be a better way to do this so that I don't have to repeat the Select Clause. I just can't seem to wrap my brain around the proper Where clause.

    Thanks

    ps. I know the syntax above is not correct. I only wanted to show the relevant code.

  • meichner (2/4/2009)


    I know there my be a better way to do this so that I don't have to repeat the Select Clause.

    There is, but it tends to have interesting performance problems. The way you're currently doing it also has performance problems in the form of parameter sniffing.

    The best way to do that is to have each of the selects in it's own procedure and call the relevant procedure from the if statement.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • GilaMonster (2/4/2009)


    meichner (2/4/2009)


    I know there my be a better way to do this so that I don't have to repeat the Select Clause.

    There is, but it tends to have interesting performance problems. The way you're currently doing it also has performance problems in the form of parameter sniffing.

    The best way to do that is to have each of the selects in it's own procedure and call the relevant procedure from the if statement.

    I am not so worried about performance in this case because the procedure in question is used to load static data. The table currently has about 20 rows and will not grow too much (if any) over time.

    Therefore I was hoping to combine the separate if statements into one.

    Thanks

  • Then this should do it.

    WHERE Status = @status OR @status IS NULL

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • GilaMonster (2/4/2009)


    Then this should do it.

    WHERE Status = @status OR @status IS NULL

    I had that solution but something bothered me about it. Since I am not a SQL expert I didn't trust myself.

    Does this mean that if I changed the where clause to:

    WHERE @status IS NULL

    that in essence the where class does not get run and that all rows are selected from the table (which is what I want when no status is supplied to the stored procedure)?

    What confuses me is that normally the Where Clause is comparing a value against a field in the table. In this case the where clause doesn't reference anything in the table.

    Thanks so much for the help.

  • meichner (2/4/2009)


    that in essence the where class does not get run and that all rows are selected from the table (which is what I want when no status is supplied to the stored procedure)?

    Oh, it'll get run, but since @status is a parameter and has a single value, that comparison will return true or false for all the rows, depending on the value of @status. So you'll either get the entire table, or 0 rows.

    What confuses me is that normally the Where Clause is comparing a value against a field in the table. In this case the where clause doesn't reference anything in the table.

    Doesn't have to be. The predicates in the where just have to be logical comparisons. They usually are based on the table, but that's not required.

    Try selecting from any table with the following two where clauses and you'll see the behaviour.

    WHERE 1=1

    WHERE 1=0

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • I can't speak to the 'performance issue' someone raised above, but I can tell you this ... I use the method described all the time, and it does do exactly what you want.

    Where clauses do not have to reference a field in the table. In this case, when your parameter is NULL, what you really want to happen is for there to be no comparisons going on, in effect, no 'where' clause to be enforced. But since you need the Where clause there for cases where the parameter IS passed in, what you are doing with the 'OR @status Is Null' is forcing every row to be evaluated as 'True' by your Where clause ... whenever a Null is passed in as @status.

  • GilaMonster (2/4/2009)


    meichner (2/4/2009)


    that in essence the where class does not get run and that all rows are selected from the table (which is what I want when no status is supplied to the stored procedure)?

    Oh, it'll get run, but since @status is a parameter and has a single value, that comparison will return true or false for all the rows, depending on the value of @status. So you'll either get the entire table, or 0 rows.

    What confuses me is that normally the Where Clause is comparing a value against a field in the table. In this case the where clause doesn't reference anything in the table.

    Doesn't have to be. The predicates in the where just have to be logical comparisons. They usually are based on the table, but that's not required.

    Try selecting from any table with the following two where clauses and you'll see the behaviour.

    WHERE 1=1

    WHERE 1=0

    Thanks so much for all the help.

  • bvaljalo (2/4/2009)


    I can't speak to the 'performance issue' someone raised above, but I can tell you this ... I use the method described all the time, and it does do exactly what you want.

    Where clauses do not have to reference a field in the table. In this case, when your parameter is NULL, what you really want to happen is for there to be no comparisons going on, in effect, no 'where' clause to be enforced. But since you need the Where clause there for cases where the parameter IS passed in, what you are doing with the 'OR @status Is Null' is forcing every row to be evaluated as 'True' by your Where clause ... whenever a Null is passed in as @status.

    Thanks. Between you and GilaMonster I am now clear on what to do.

Viewing 9 posts - 1 through 8 (of 8 total)

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