CODEWITHSUNDEEP
sqlsql-serversql-server-2008stored-proceduresssms
C. Brendan Law
C. Brendan Law

Reputation: 41

How to prevent SQL Stored Procedure Alters while the stored procedure is running?

We have a stored procedure that runs hourly and requires heavy modification. There is an issue where someone will edit it while the stored proc is running and will cause the stored proc to break and end. I am looking for an error to pop up when someone tries to edit a stored procedure while it is running, rather than breaking the execution.

It's a sql server agent job that runs hourly, I get "The definition of object 'stored_procedure' has changed since it was compiled."

Is there something I can add to the procedure? A setting?

Upvotes: 0

Views: 2608

Answers (2)

jthalliens
jthalliens

Reputation: 534

I think you can use a trigger at the database level in order to prevent changes and within the object apply validations for the running stored procedure, something like this:

USE [YourDatabase]
GO


ALTER TRIGGER [DDLTrigger_Sample]
    ON DATABASE
    FOR CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE
AS
BEGIN    
    IF EXISTS (SELECT TOP 1 1
                    FROM sys.dm_exec_requests req
                    CROSS APPLY sys.dm_exec_query_plan(req.plan_handle) sqlplan WHERE sqlplan.objectid = OBJECT_ID(N'GetFinanceInformation'))
    BEGIN
        PRINT 'GetFinanceInformation is running and cannot be changed'
        ROLLBACK        
    END 
END

that way you can prevent the stored procedure being changed during execution, if it's not being executed changes will be reflected as usual. hope this helps.

Upvotes: 1

dfundako
dfundako

Reputation: 8314

You should do some research and testing and confirm this is the case. Altering a SProc while executing should not impact the run.

Open two SSMS windows and run query 1 first and switch to window 2 and run that query.

Query 1

CREATE PROCEDURE sp_altertest
AS 
BEGIN 

SELECT 'This is a test'

WAITFOR DELAY '00:00:10'
END
GO 

EXEC sp_altertest

Query2

alter procedure sp_altertest AS

BEGIN 

SELECT 'This is a test'

WAITFOR DELAY '00:00:06'
END
GO 
Exec sp_altertest

Query 1 should continue to run and have a 10 sec execution time while query 2 will run with a 6 sec runtime. The SProc is cached at the time of run and stored in memory. The alter should have no impact.

Upvotes: 0

Related Questions