Results 1 to 10 of 10
  1. #1
    Join Date
    Dec 2002
    Location
    Sunny Florida
    Posts
    121

    Unanswered: Dynamic SQL in procedure compiles but does not run

    The reason why is because we offer several "lines of coverage" One company may use our app for Workers' Comp and has it's own set of tables. They may use it for Liability that too has it's own set of tables and they may have both. And by using only one usually means that is th eonly set they have.

    We have standard report for each line. Since the reports call a stored proceudure I've written it with dynamic SQL in an attept to only have one stored procedure. The idea is that I will pass the procedure the line of coverage and the corrects set of statements will run. I made them dynamic SQL because I figured it would puke when trying to compile and hoped it would not check if a table exist until runtime due to the iff statement.

    Well, it is parsing all statements regardless of the instructions from the parameters. Anyone know of any tricks to get around this?

  2. #2
    Join Date
    Dec 2002
    Location
    Sunny Florida
    Posts
    121
    I'm goint to try removing the IF statements for the lines of coverage and just do something like:

    case @lob when 'WC' then 'WC' else 'GL' end + 'CCLM1'

    I suspect this will work just fine.

  3. #3
    Join Date
    Dec 2002
    Location
    Sunny Florida
    Posts
    121
    I found the cause. I had hard coded the wrong line of coverage in an area of the procedure so that explains why it was looking for the opposite tables! I'm still going to do the case statement above. Since I've got it in one proc I should cut down what was four queries to two!

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    ...and the mess that you leave behind I usually call "why don't you just shoot me, right now?"
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  5. #5
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040

    Exclamation

    [QUOTE=DBA-ONE]We have standard report for each line. Since the reports call a stored proceudure I've written it with dynamic SQL in an attept to only have one stored procedure. The idea is that I will pass the procedure the line of coverage and the corrects set of statements will run. I made them dynamic SQL because I figured it would puke when trying to compile and hoped it would not check if a table exist until runtime due to the iff statement.
    QUOTE]

    But you ignore the fact that the optimizer will recompile your Dynamic SQL
    ** every **
    time it executes, so you are trading writing four efficient procs running well for one extremely inefficient proc that runs poorly.

  6. #6
    Join Date
    Dec 2002
    Location
    Sunny Florida
    Posts
    121
    That is actually fine. Because these reports are not run every second of the day so the overhead on the server is much more favorable then having to change several procedures and statements. I'd much rather update one procedure, one file and have to put that one file in a single location. See, we support Oracle and SQL Server so for each procedure I need one for comp, one liability, and one each of those for SQL and Oracle. Now do you see why I'm willing to give up a little on the server end to make the maintinence easier? When we roll something out it can be an enourmous pain having to put all these files in different places.

  7. #7
    Join Date
    Dec 2002
    Location
    Sunny Florida
    Posts
    121
    By the way, I test the old version to the new. While the optimizer may have more work to do there was no difference to speak of in the time to execute. Not bad for my sloppy procedure huh?

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Recompilation expense is minimal and only becomes an issue when a statement might be executed hundreds of times each minute.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  9. #9
    Join Date
    Dec 2002
    Location
    Sunny Florida
    Posts
    121
    Clients may execute these once a day. Usually at month end.

  10. #10
    Join Date
    Dec 2002
    Location
    Sunny Florida
    Posts
    121
    Quote Originally Posted by rdjabarov
    ...and the mess that you leave behind I usually call "why don't you just shoot me, right now?"
    At least it doesn't happen too often!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •