How can we help you today? How can we help you today?

Generate CREATE + ALTER Stored Procedure Script

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


GurpreetGill
0

Comments

12 comments

Sort by
  • GurpreetGill
    any help.

    GurpreetGill
    0
  • GurpreetGill
    Please help...
    GurpreetGill
    0
  • Ivo_Miller
    Hi,

    Apologies for the delayed response. This is currently a limitation of SQL Change Automation programmable object handling (See permission handling under https://documentation.red-gate.com/sca/developing-databases/concepts/migrations/programmable-objects).

    You can manually alter the programmable object but it is then liable to be overwritten if you are not careful. I have raised an internal ticket for this and we will see if there are any improvements that can be made here. We will get back to you when we know.

    Kind Regards,

    Ivo Miller
    Ivo_Miller
    0
  • Ivo_Miller
    Hi,

    Apologies for the delayed response. This is currently a limitation of SQL Change Automation programmable object handling (See permission handling under https://documentation.red-gate.com/sca/developing-databases/concepts/migrations/programmable-objects).

    You can manually alter the programmable object but it is then liable to be overwritten if you are not careful. I have raised an internal ticket for this and we will see if there are any improvements that can be made here. We will get back to you when we know.

    Kind Regards,

    Ivo Miller
    Ivo_Miller
    0
  • Ivo_Miller
    Hi,

    Apologies for the delayed response. This is currently a limitation of SQL Change Automation programmable object handling (See permission handling under https://documentation.red-gate.com/sca/developing-databases/concepts/migrations/programmable-objects).

    You can manually alter the programmable object but it is then liable to be overwritten if you are not careful. I have raised an internal ticket for this and we will see if there are any improvements that can be made here. We will get back to you when we know.

    Kind Regards,

    Ivo Miller
    Ivo_Miller
    0
  • Ivo_Miller
    Hi,

    Apologies for the delayed response. This is currently a limitation of SQL Change Automation programmable object handling (See permission handling under https://documentation.red-gate.com/sca/developing-databases/concepts/migrations/programmable-objects).

    You can manually alter the programmable object but it is then liable to be overwritten if you are not careful. I have raised an internal ticket for this and we will see if there are any improvements that can be made here. We will get back to you when we know.

    Kind Regards,

    Ivo Miller
    Ivo_Miller
    0
  • Ivo_Miller
    Hi,

    Apologies for the delayed response. This is currently a limitation of SQL Change Automation programmable object handling (See permission handling under https://documentation.red-gate.com/sca/developing-databases/concepts/migrations/programmable-objects).

    You can manually alter the programmable object but it is then liable to be overwritten if you are not careful. I have raised an internal ticket for this and we will see if there are any improvements that can be made here. We will get back to you when we know.

    Kind Regards,

    Ivo Miller
    Ivo_Miller
    0
  • GurpreetGill
    Ok Thanks Ivo
    GurpreetGill
    0
  • David Atkinson
    @GurpreetGill - what version of SQL Server are you using? We are investigating changing the programmable object format to the following, but this syntax is only supported in SQL Server 2016+.

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

    David Atkinson
    0
  • EricLamontagne
    @David Atkinson That would be great if you would be able to generate the Create or Alter syntax when the target SQL Version is 2016+

    Thanks,

    Eric
    EricLamontagne
    0
  • jtoussaint
    That would be great if you would be able to generate the Create or Alter syntax when the target SQL Version is 2016+
    jtoussaint
    0
  • David Atkinson
    Setting programmable objects to use CREATE OR ALTER is now a configurable setting (UseCreateOrAlterForRerunnableScripts) as described in the documentation.
    David Atkinson
    0

Add comment

Please sign in to leave a comment.