Dynamically Created Stored Procs??

Okay. Scenario:

We as a company have several companies that request custom reports from us. However, the custom reports that they select will generally always have the same fields (and formulas) once they make up their minds on what works for them.

From what I know, Stored Procs are usually faster at running things unless the parameters are changing too drastically that they get passed.

This being the case, it would seem like a good case for creating a stored proc per report. This would alleviate the possability that the server chooses an execution plan that works great some of the time but the rest of the time run lousy.

So these thoughts being laid out, is there a good,nice,easy, convenient way to generate/alter a stored proc, either by another stored/extended proc, or by dynamic sql going to the server?

Or if not, is there some round-about yet effective way of doing this?

Thank you in advance for any help.
-ZanderBI guess I don't understand why you feel you need to be able to alter existing stored procedures on demand. Automating something like this would take a lot more programming skill and development time than just copying an existing procedure and modifying it to create a new one.|||The point of the matter is that right now, there is no efficient on demand procedure (that I know of). Any dynamic SQL must rely on having an execution plan made by the server every time (as I understand).

When you have web enabled reports that already take several minutes, it becomes key that optimizations are made. At the same time, having something that is dynamic, but generally used enough to say it's called all the time is something that would be worthy of being called by a stored proc. Perhaps even something equivical would be nice that is not as static, but there is nothing I know of that does that for dynamic SQL.

If you had a web server that server for 4 items 80% of the time, but those 4 items changed frequently (like once a week) you could say there would be a good argument for something like this, could you not?

The structure of the where statement can cause the execution plan to be changed.

So my question again is why you wouldn't want a dynamically created stored proc for this.

Are dynamic queries going to have a cached execution plan if the same ones are called enough?|||When the procedure is supposed to be used only once, it is perhaps not a good idea to create the procedure and the drop it after having executed it once.

Another approach that i have tried, is to create what is called an anonymous block. This is the body of the procedure without the create procedure part. This is a T-sql block that is sent to the database and executed as if it where a straight forward select. It can contain exeption handling and give all the functionality you expect from a procedure, but will not impact the system dictionary. The overhead will probably be less than the procedure approach since the system dictionary is not updated.

n both cases you will get one parse, optimize, compile and execute. The database will perform it as optimal as a procedure. I have tried it with code performaing a cursor loop and doing a couple of queries for each row in the cursor. This is code that is much better to perform inside the database than from an external application.|||Nevermind. It is possible through a standard connection to the server (with right permissions) to change/add stored procs. I didn't know if this was a possibility or not. It only surprises me that noone said it's possible with dynamic SQL statements.|||Originally posted by ZanderB
When you have web enabled reports that already take several minutes

Every new request becomes a log of the report in a table.

The get a list and select what they want... the table stores the proc name which gets executed

You'll come to a point when you've created almost all variations.

And if you're telling us that you have to wait several minutes for a web based report...that's waaaaaaaaaaaaaaaaaaaay too long

You need to build a process to denormalize the data so they pop...

I would imagine an in/experienced user would think the damn thing is broken...|||Originally posted by ostrande
anonymous block.

Don't look now...but your Oracle is showing...

:D|||No one said it was possible with dynamic SQL because no one wants to recommend dynamic SQL. Dynamic SQL is usually slower and less efficient, although admittedly in some cases where there are complex conditions dynamic SQL can be faster. In these cases, though, the dynamic SQL is best created and executed within the procedure given the parameters supplied. The procedure itself is not modified.

And bear in mind that having a pre-compiled stored procedure does save execution time - like 1 or 2 seconds (at the most!) for each time the procedure is executed. It does not take that long for SQL server to compile a procedure. If your querys are taking 3-4 minutes, the problem is not in the compilation time.|||Yo, Blind dude...

ever wait MINUTES for a web based report?

Dynamic way to change Font, Size, etc.

Hi,
I have one report format for 20 companies. Some of them require differnt
fonts and font sizes. I don't want to duplicate reports because they need
different font format. Is there a dymanic way to change font using one report
only? Thanks.
ChuckThere are two possible ways to do this:
1) Have your font settings in expressions that are populated by report
parameters or data. (It really works!) You can set default parameter
values.
2) Create a process that dynamically updates the RDL (report source) for
each company. This would mean inserting an extra step at either deployment
time or run time.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Chuck" <Chuck@.discussions.microsoft.com> wrote in message
news:3B1DF44A-14C7-4D90-B9D3-A3E67C913E0F@.microsoft.com...
> Hi,
> I have one report format for 20 companies. Some of them require differnt
> fonts and font sizes. I don't want to duplicate reports because they need
> different font format. Is there a dymanic way to change font using one
> report
> only? Thanks.
>
> Chuck|||Now I get some ideas to work on my project. Thanks so much for your reply.
-Chuck
"Jeff A. Stucker" wrote:
> There are two possible ways to do this:
> 1) Have your font settings in expressions that are populated by report
> parameters or data. (It really works!) You can set default parameter
> values.
> 2) Create a process that dynamically updates the RDL (report source) for
> each company. This would mean inserting an extra step at either deployment
> time or run time.
> --
> Cheers,
> '(' Jeff A. Stucker
> \
> Business Intelligence
> www.criadvantage.com
> ---
> "Chuck" <Chuck@.discussions.microsoft.com> wrote in message
> news:3B1DF44A-14C7-4D90-B9D3-A3E67C913E0F@.microsoft.com...
> > Hi,
> >
> > I have one report format for 20 companies. Some of them require differnt
> > fonts and font sizes. I don't want to duplicate reports because they need
> > different font format. Is there a dymanic way to change font using one
> > report
> > only? Thanks.
> >
> >
> > Chuck
>
>|||Another solution without having to add all those Parameters would be to
create a custom class that returns the font and font size etc. Then reference
the class in an expression. I have done this and it works great. Then you
could either distrubute a seperate class for each customer or have the class
read the information in from file and just distrubute a seperate file for
each.
See "Writing Custom Code" in the RS Help
Dave
"Chuck" wrote:
> Hi,
> I have one report format for 20 companies. Some of them require differnt
> fonts and font sizes. I don't want to duplicate reports because they need
> different font format. Is there a dymanic way to change font using one report
> only? Thanks.
>
> Chuck