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?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.|||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!|||...and the mess that you leave behind I usually call "why don't you just shoot me, right now?"|||[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.:(|||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.|||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?|||Recompilation expense is minimal and only becomes an issue when a statement might be executed hundreds of times each minute.|||Clients may execute these once a day. Usually at month end.|||...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!