Post reply

Best Practices - Variations of the same report (Seniors only)

  • June 14, 2011 at 3:34 pm

    #242109

    Hello!

    Let's see if u can help me (I think so).

    I have a report that runs regularly to all customers in my database. However, over time, has emerged the need for exceptions to handle peculiarities among them. In this case, I wrote a code like the following:

    select @ sql2 = @ sql2 + CASE WHEN @ alias_rede = 'AMAZON' then 'and pri.storecode <> 9'')'

    When alias_rede @ = 'ShopTo' then 'and not exists (select 1

    from pdv.dbo.store lva

    WHERE lva.code_rede = pri.code_rede

    and lva.nu_da_loja = pri.nu_da_loja

    and lva.flag_store'''') =''''CD'') '

    When @ alias_rede in ('SUBMARINE', 'SAMS') then 'and not exists (select 1

    from pdv.dbo.store lva

    WHERE lva.code_rede = pri.code_rede

    and lva.nu_da_loja = pri.nu_da_loja

    and lva.flag_store'''') =''''CD'') '

    else ''')'

    end

    @ sql2 is a variable that contains a query and, according to the store, the filters are added.

    These exceptions occur frequently and want to know what the best practice to be used:

    1 - Create a stored procedure for each exception (spCallReportStores, spCallReportStoresWithSAMS ...)

    2 - Register the exceptions and make them configurable (seems to be the best practice, but how to work all exceptions in a generic way?)

    3 - New suggestions

  • June 14, 2011 at 4:25 pm

    #1338708

    What you posted is not technically SQL. It's application code written in the data layer masquerading as SQL.

    Reformatted:

    SELECT @sql2 = @sql2 + CASE WHEN @alias_rede = 'AMAZON' THEN 'and pri.storecode <> 9'')'

    WHEN @alias_rede = 'ShopTo' THEN 'and not exists (select 1

    from pdv.dbo.store lva

    WHERE lva.code_rede = pri.code_rede

    and lva.nu_da_loja = pri.nu_da_loja

    and lva.flag_store'''') =''''CD'') '

    WHEN @alias_rede IN ('SUBMARINE', 'SAMS') THEN 'and not exists (select 1

    from pdv.dbo.store lva

    WHERE lva.code_rede = pri.code_rede

    and lva.nu_da_loja = pri.nu_da_loja

    and lva.flag_store'''') =''''CD'') '

    ELSE ''')'

    END

    Go with option 1 and move away from using dynamic sql.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • June 14, 2011 at 6:35 pm

    #1338734

    ... and please don't be so pompous to label your post as "Seniors Only". You might just talk a couple of us seniors out of helping with an attitude like that. 😉

    --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)
    Intro to Tally Tables and Functions

  • June 14, 2011 at 9:17 pm

    #1338742

    About the topic title:

    Sorry, wish I could fix this, but I have no idea how to edit the topic title.

    Moreover, I have no doubt that juniors could help me with simple suggestions.

    On the question: Have many versions of procedure did not leave the database more difficult to administer and does not make maintenance more difficult?

    I should have a main procedure that calls the secondary ones?

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

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