I always thought that updating a stored procedure was something I could do while users were actively using the system.
But I am currently testing out an update to a stored procedure and while a long running call to the sproc was being executed (it was 4+ min into the call), I updated the sproc. (I was planning to try a different query plan in a seperate window.)
When I went back to the long running one, I had this error:
The definition of object 'MySprocName' has changed since it was compiled.
This error seems to say that any sproc that is being actively executed will fail if the definition of that sproc is updated. (I thought that once a sproc started running, it would use that plan for the rest of the run despite changes to the definition.)
Is this true? Do I need to have downtimes to update definition of a sproc?