Generate CREATE + ALTER Stored Procedure Script

GurpreetGillGurpreetGill Posts: 26 Bronze 1
in SQL Change Automation
Hi

By default, SCA generated the Stored Procedure Script as  DROP + CREATE 

IF OBJECT_ID('[dbo].[My_SP]') IS NOT NULL
DROP PROCEDURE [dbo].[My_SP];

GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE [dbo].[My_SP]
AS
SELECT xx66 FORM YY
GO


I want to generate script CREATE + ALTER


IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[My_SP]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[My_SP] AS' 
END
GO 

ALTER PROCEDURE [dbo].[My_SP]  
BEGIN
       SELECT xx66 FORM YY
END

GO


The problem with DROP + CREATE approach is, we lost all the grant permission to stored procedure.
on the other hand, with CREATE + ALTER approach we will not lost any grant permission.

I know, i can use post-deployment to apply grant permission again. Is CREATE + ALTER possible with SCA.

Thanks
Gill


Tagged:
· Share on Twitter

Comments

Sign In or Register to comment.