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?

Dynamically change report datasource based upon parameter.

I currently have one report that I would like to be able to use to report
off of stored procs on multiple servers. And am hoping someone can point me
in the right direction.
Example:
Report A will run off of storedProc1 which exists in every database.
However, the specific server and database to use will depend upon the user
currently logged in.
Currently I am trying to make use of the custom dataset extension (by Teo
Lachev) to report off of an XML string. Unfortunately, I am having fits
trying to get it to work and don't even know if this is the best way.
Any help would be appreciated.Various approaches for dynamic database connections in RS 2000 have been
discussed on this newsgroup:
* Use a custom data processing extension (as you currently do)
* Use the linked server functionality of SQL Server; please check this
thread:
http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=848bac6b-98a2-4de7-abfd-bf199a99b660&sloc=en-us
* If the databases are on the same server, use a dynamic query text (i.e.
="select * from " & Parameters!DatabaseName.Value & "..table")
* If you're just toggling between two or three databases, you can publish
the same report 3 times with 3 different names using 3 different data
sources and write a main report that shows/hides the correct subreport based
on whatever criteria you want.
Native support (expression-based connection strings) is available in RS
2005.
Hope this helps,
Robert
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tarik Peterson" <tarikp@.investigo.net> wrote in message
news:O4%23lPKA9EHA.960@.TK2MSFTNGP11.phx.gbl...
> I currently have one report that I would like to be able to use to report
> off of stored procs on multiple servers. And am hoping someone can point
me
> in the right direction.
> Example:
> Report A will run off of storedProc1 which exists in every database.
> However, the specific server and database to use will depend upon the user
> currently logged in.
> Currently I am trying to make use of the custom dataset extension (by Teo
> Lachev) to report off of an XML string. Unfortunately, I am having fits
> trying to get it to work and don't even know if this is the best way.
> Any help would be appreciated.
>