Updating live/running Stored Procedures / Functions

[debnarayan]

Hi, My application requires certain reporting queries to be totally dynamically determined from application level. What I have done [rather, am doing] is build the query as indicated from UI and store it as a SP in the database. The confusion is, say, someone is running the reporting SP, [which takes around 30 secs time to get executed and start returning data], and at the same time someone else trigerred an Alter on the SP. I do not want any one to get an exception, not even a timeout. So what I have planned is to have a list of the dynamic SP's in DB table, and indicate by a flag, whether it is in use, or not, also whether it is further callable or not. So we can effectively give proper messages instead of exception, if any [I actually do not know, whether there will be an exception] So, my questions are: 1. What happens when someone calls for an Alter script on a SP while it is running 2. What if, due to some problem, like a connection drop or something, the running query stops executing. As the flags are shared, I cannot run these on a transaction. Essentially, I do not know what happens when the connection over which the query is invoked gets dropped midway in execution. Does the query stop executing, raising an error, or does it complete the execution, and stop silently? Thanks in advance

[Ashfield]

Why not just trap the error returned if the SP is running when you try and do the alter, then wait x seconds and retry? Naturally you would need to put some sort of check in to prevent endless loops. This would also cover connectiosn dropping etc.

Bob Ashfield Consultants Ltd Proud to be a 2009 Code Project MVP

[Bassam Saoud]

I dont like your design. My advice to you is dont put the dynamic query in a stored procedure instead put it in your application business layer. you are using dynamic sql, so you are not going to make use of the query plan caching etc [its going to be recompiled]. This being said, if you insist on your design, create a table that keep records of whats running. When you get a request of changing an sp, query that table and check if the sp is running. if the sp is running report back a friendly error.

[Mycroft Holmes]

I'll back up Bassam, your design sucks (following the theme in the Lounge). ALTERing a stored proc to meet these requirements is nuts. Change your design to generate the SQL in the business layer and exec the sql string in your proc (I hate this as you are left open to sql injection) or parameterise EVERY option the user can select and pass in the data as parameters and build your select in the proc.