November 18, 2003 at 7:25 am
Does anyone use temporary stored procedures? From time to time I have thought about temporary stored procedures, however I can never think of a reason to use one.
Robert W. Marda
SQL Programmer
bigdough.com
The world’s leading capital markets contact database and software platform.
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
November 18, 2003 at 7:50 am
I have used them.
The client app would request various sets of data from the server. These sets of data sometime contained data for the last 6 months or for the last 48 months so a stored procedure seemd a good idea for reuse. The temporary stored procedure produced the data for a single month and the parent procedure called it the appropriate number of times. I also put the creation of the temporary SP into a stored proc so that I could call it from any other proc that needed to execute the temporary proc.
The reason for them being temporary SPs is that the name of the table containing the data varied for each call from the client.
The parent procedure might call the temp proc hundreds of times during each client request and I thought it was quite a reasonable solution.
I'm not sure about how efficient it was compared to using exec sp_executesql with an SQL String and output paramaters but I'm sure someone here knows.
I originally wrote this app on SQL 6.5 and never really had the time to re-engineer it for all the new features in SQL 2000.
Jeremy
November 19, 2003 at 1:39 am
My argument against it would be: you have to give the user permission to create objects. Do you want to do that?
November 19, 2003 at 1:45 am
The only permission I give the users is SELECT permission on the database tables and, of course, execute permission on the stored procedure they call from the client app.
I am aware of the issues with dynamic SQL (which I use as well) but the users do not enter free format text - it is all selections from listboxes.
Jeremy
November 19, 2003 at 2:32 pm
I use execute(string) and sp_executesql also, for about the same reasons that Jeremy said. I'd be lost without them, especially the execute(string) one. I don't create stored procedures as objects, just run the string. My timeline is ~30 minutes instead of a month is why. It's neat to see someone else that has needed this functionality.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply