Syntax error in ALTER PROCEDURE in a DB update script
I have a script to update my database for a new release of my web app . In this update i need to alter a strored procedure. I have an ALTER PROCEDURE script that works fine when run on its own, however when I drop it into my update script and run it I get the errors "Incorrect syntax near the keyword 'PROCEDURE'." and "Must declare the scalar variable "@age"." What am I doing wrong here? The script is as follows:
BEGIN TRY
BEGIN TRANSACTION
-- ==================================================================
-- v0.1 to v0.2
-- ==================================================================
IF EXISTS
(
SELECT * FROM SystemParameters WHERE Name = 'Version' AND Value = '0.1'
)
BEGIN
-- ==============================================================
-- Changed Stored Procedures
-- ==============================================================
ALTER PROCEDURE ClearCache
@age int = 120
AS
BEGIN
DECLARE @timestamp DATETIME
SELECT @timestamp = DATEADD(MINUTE, -@age, GETDATE())
-- Clear old searches
END
-- ==============================================================
-- Update the Version Number
-- ==============================================================
UPDATE SystemParameters SET Val开发者_开发技巧ue = '0.2' WHERE Name = 'Version'
END
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
-- Report the Error
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
SELECT
@ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState)
END CATCH
Any help would be appreciated :)
I doubt you can alter the procedure inside the IF clause Try using it as a dynamic SQL
EXEC
(
'ALTER PROCEDURE ClearCache
@age int = 120
AS
BEGIN
DECLARE @timestamp DATETIME
SELECT @timestamp = DATEADD(MINUTE, -@age, GETDATE())
-- Clear old searches
END
'
)
精彩评论