• Roland Boon (unregistered)

    Ah, so it wasn't a glitch in the Matrix. Yesterday I saw a very nice new layout for WTF. I thought: WTF! Hope you guys get it up and running.

  • wow (unregistered)

    But what about option 4?

  • David Grant (unregistered)

    Bingo. #4 is missing. Definite WTF.

  • Brilliant (unregistered)

    I know what my new function will look like

    object SuperFunction(string option, object[] params);

    Sweeeet!

  • Guayo (unregistered)

    big deal... it's just the app entry point. why is that a WTF?

    Option 4 is reserved for future enhancements, like backing up sensitive data...

    EXEC xp_cmdshell 'copy c:\my pr0n*.* e:\backup\my pr0n', NO_OUTPUT

  • Bergstrom (unregistered)

    This is how a lot of PHP coders I know develop.. do everything on one page, and use an $action or something to determine the state.. makes for some mega large function files.

  • Sean Kleinjung (unregistered)

    Yup...I inherited a PHP webapp that used one .php file (called 'get_login.php') that generated almost all of the site's pages. A huge if-else block and gawdy comments divided the madness.

    Later, when developing a new application, the original developer advocated doing it the same way...

  • Jeff S (unregistered)

    At least all of the option codes are perfectly meaningful. e.g., option "6" means @name is the customerID. It's very intuitive.

  • Jon (unregistered)

    <I>Returns a single record from a table with only 1 record!!</I>

    WTF?

  • skicow (unregistered)

    I agree with Jeff S, I like how they have options for 1-3, 5-9, and B,C, and Z very intuitive.

    I like how 7, B, and Z somehow return records via a varchar....very nice.

  • Lore Weaver (unregistered)

    Ya, they do this in PHP, but this is T-SQL, not some stateless webpage.

    This is 100% stupid. If this person worked for me, they'd be fired on the spot.

  • G Dawg (unregistered)

    I don't see a big problem... in fact I can see where this is necessary. If you don't have the ability to change production code which is using user-driven input to a stored proc then this makes perfect sense. You cant go adding new procs in that case. This is a flexible and robust solution to a situation like that.

  • Lore Weaver (unregistered)

    G Dawg,

    So making changes to a stored procedure that many things call has less possible impact on the stability of the production environment?

    I guess we have to agree to disagree :D

  • xanderd (unregistered)

    G Dawg, don't even try to justify this. In a production environment, adding a code block to an existing SP is no more flexible or robust than adding a new SP.

    Note that whatever code blocks call spCustomerList2, they obviously contain custom code to handle the various datasets output by the specified 'option'. These custom code blocks could just as easily invoke individual SPs as this idiocy.

  • Lon (unregistered)

    Aside from the obvious negative things about this code, the database usually caches the query plan on compiling the stored procedure, and having lots of unrelated sql statements usually ends up with a very poor performing stored procedure.

  • foxyshadis (unregistered)

    If you think about it, there's a very good justification for this: The programmer wanted to make sure that everyone who used his functions read the docs (comments) before they assumed they knew how it worked! Brilliant! No more newbies trying to use his masterful code; this is reserved for the competent and curious only. Just make sure every error message spits out "RTFM!!!" to keep it professional.

  • FooManchu (unregistered)

    @Lore Weaver
    >
    > Ya, they do this in PHP, but this is
    > T-SQL, not some stateless webpage.
    >
    > This is 100% stupid. If this person
    > worked for me, they'd be fired on
    > the spot.

    And that's why you're an employee, not an employer.

    It's dumb code, but it's not as dumb as you.

  • Fasura (unregistered)

    I think Lore's point is perfectly valid. There's enough desperate CS graduates around to fire any that are usuless and still be able to fill your ranks. Decimate the worthless and let the competent work.

  • Mike Woodhouse (unregistered)

    > the database usually caches the query plan on
    > compiling the stored procedure

    Even worse: the query plan is only generated when the proc is first executed ! So each time it's changed, the performance may (will?) as the first call will most likely use a different option to the first one the last time.

    Of course, if the same individual was responsible for the schema, there's probably only one table and it won't have any indexes, so performance issues won't be a problem...

  • Dave Brookes (unregistered)

    Some interesting views here!
    This sproc is called exclusively from a single method in a mid tier com+ dll. The ex-employee had full control over all the source and database. What amazes me is the mid tier does no business logic, just blindly passes the record set back to 12 different asp pages depending on the function. Here in vbScript is where all the important stuff takes place. The guilty party used to spend a week each month importing masses of data from the ERP system into database 1, then creating a de-normalised table per report. (He didn’t understand joins). Transfer the whole lot onto database 2 (different server) followed by some more formatting. You can bet it now all happens automatically on a single server! Grrr, I could rant forever.

  • ABH (unregistered)

    Mike Woodhouse
    > Even worse: the query plan is only generated
    > when the proc is first executed ! So each > time it's changed, the performance may
    > (will?) as the first call will most likely
    > use a different option to the first one the
    > last time.

    Actualy Mike, the sproc will be recompiled every time it is called as the previous query plan will be invalid for the new option, unless by some fluke it is called with the same option parameter twice in a row.

  • Guayo (unregistered)

    @ABH
    What you mean?... AFAIK parameter values doesn't force a recompilation in SQL Server (which is a good thing).

  • ABH (unregistered)

    Guayo,

    No, but if each section under each option has different SQL code, the proc will have to recompile its query plan every time, as it won't be able to use the query plan from last time, as it will be an entirely different query.

  • Brian Scott (unregistered)

    Yeah, the query plan is only created the first time the procedure is executed.


    If it was a situation like G Dawg suggested then this procedure should call external procedures that do the minimal work they need to do for the results. That way, each one has a plan more suited for the data it needs.


    You would have to explicitly use WITH RECOMPILE in order to get the procedure or any EXECUTE's to recompile, which would be a performance hit here too.

    Otherwise, a procedure normally only recompiles if you drop and recreate the procedure, change the schema of related objects, etc...

    This is so screwed up... Setting asside everything else that is wrong, it would be more performant to execute this sql in the applications source code.

  • Brian Scott (unregistered)

    ABH,

    The query plan isn't a hard coded path the procedure takes through the database. It's just an optimization plan. Other routes can be taken, different data can be returned, but if it varies from the original plan, it just will not run at an optimized level.

  • Belenkas (unregistered)

    :::For those who think it's a PHP coding style:::

    it has nothing to do with PHP, as examples of such approach can be found everywhere. The only reason it's sometimes seen on PHP - many "developers" begin careers with this language.

    So it's really dumb to associate this kind of coding with PHP.

Leave a comment on “MULTI/SQL”

Log In or post as a guest

Replying to comment #:

Log Out

« Return to Article