Post reply

query scenario question

  • March 27, 2015 at 2:29 pm

    #316745

    Hello, I have a table with an IsProcessed column. This field is null by default. A scheduled job sets this field value to 1 after it has processed the row. So what's the most elegant way to query all the rows in the table if the @IsProcessed query parameter is null?

  • March 27, 2015 at 3:13 pm

    #1787044

    WHERE IsProcessed IS NULL

    If you want to check for new rows to process while existing rows are being processed, look into the "READPAST" hint.

    If you just need to quickly find only NULL values, look into filtered indexes.

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • March 27, 2015 at 3:42 pm

    #1787051

    A null parameter should essentially not be applied as a filter. A null parameter means the user did not want to filter by that parameter.

  • March 27, 2015 at 3:45 pm

    #1787053

    Sorry:

    WHERE (@IsProcessed IS NULL OR @IsProcessed = <table_column>)

    Edit: general pattern, you may need to change it slightly.

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • March 27, 2015 at 4:50 pm

    #1787070

    yep that's the right way thanks

  • March 29, 2015 at 3:55 am

    #1787155

    Quick thought, it may be worth looking into splitting the table in two, one for the "Tasks" and another for the "Process attempts", so instead of updating the column a new row is added to the latter table. This kind of "flag" update is a very common cause for blocking on a busy system.

    😎

    +-----------+ +--------------+

    | TASK | | PROCESSED |

    +-----------+ +--------------+

    | TASK_ID |-+--, | PROCESSED_ID |

    | <details> | '-----0<| TASK_ID |

    +-----------+ | <details> |

    +--------------+

  • March 29, 2015 at 2:45 pm

    #1787185

    For me, the "right way" would be to constrain the column to being NOT NULL and default it to zero. Makes life a whole lot simpler.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 7 posts - 1 through 6 (of 6 total)

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