8

If a stored procedure (or view) is currently in use within a long running cursor, and I alter that stored procedure, will the cursor continue using the old instance of the stored procedure until the cursor is finished?

Improve this question
3
  • Sorry, I meant until the cursor is finished.
    – J.D.
    Nov 14, 2019 at 22:26
  • What do you mean by in use within a cursor? There is code looping through the cursor and EXEC-ing it?
    – Martin Smith
    Nov 15, 2019 at 9:08
  • @MartinSmith Correct, the code that is looped by the cursor is executing a procedure (or selecting from a view.)
    – J.D.
    Nov 15, 2019 at 14:55

1 Answer 1

Reset to default
7

So per @LowlyDBA's suggestion I decided to test this for myself (to a degree). I was actually in the middle of debugging a hot production issue so couldn't really stop to write out the code for a test case. But the code I'm debugging is this scenario. So I made my update to the procedure and looked at the running queries and saw the query text changed from before and after my change to the procedure as the cursor continued to iterate.

Improve this answer
5
  • 1
    I totally skipped over the word "cursor" in this, thought you were asking about modifying it mid-execution in a single execution, sorry I didn't actually write up an answer. Glad you figured it out, though :)
    – LowlyDBA - John M
    Nov 14, 2019 at 19:24
  • 2
    Are you sure this reflects the actual behaviour of the system, and not just a limitation of the debugger display? I would try to change the procedure to return completely different values and look at the result, not the debugger windows.
    – IMil
    Nov 15, 2019 at 4:34
  • That test with the debugger seems unoptimal too. Does that even reflect what the engine does? Where does the debugger get the code from. Is the cursor static or dynamic? I'm not sure I trust this answer to be absolutely true
    – Tom V
    Nov 15, 2019 at 5:39
  • 1
    Easy to test without the debugger dbfiddle.uk/…
    – Martin Smith
    Nov 15, 2019 at 15:03
  • Sorry, I used the term debugging loosely, not actually using SSMS's debugger to step into the code. My exact scenario to test this was: 1) A cursor (I'm not sure if it was static or dynamic, there was no declaration in that regard) that executes a procedure was in the middle of looping. 2) Using the sys.dm_exec_requests and sys.dm_exec_sql_text DMVs I was able to see the query text of the procedure on each execution. 3) While the cursor was in the middle of looping, I altered the procedure by adding a comment and made some logic changes. 4) I saw the query text change between cursor loops.
    – J.D.
    Nov 15, 2019 at 15:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.