Whether Drop and Create Stored Procedure is best practice for deployment (or) Alter Stored Procedure is best practice for deployment?

chokka
chokka used Ask the Experts™
on
Whether Drop and Create Stored Procedure is best practice for deployment (or) Alter Stored Procedure is best practice for deployment?

I am working on automating a process of deploying Stored Procedures across the Servers through SSIS Package.

My clarification is, when we deploy an existing stored procedure across the environments , Whether we can drop and create (or) Alter would be fine?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solution Only
Pratik SomaiyaApplication Development Associate

Commented:
Ohh okay, I got your question now.

You should use ALTER in case of the stored procedure so as to maintain the permissions associated with that stored procedure.

Regards,
Pratik
Pratik SomaiyaApplication Development Associate

Commented:
whenever you DROP and CREATE you may lose the permissions associated with that object.
SStory

Commented:
I haven't used SSIS. I am curious though as to why you wouldn't drop if exists and the recreate them. Are you worried about losing permissions on them? If so can't it run something to GRANT those as needed?
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Find out now!
chokkaStudent

Author

Commented:
@SStory, My question is " Which is the best practice? "

Either Drop and Create
(or)
Alter


I understand about losing the permission on Drop and Create. We can add a Grant Permission syntax after create.

  1. Drop Procedure
  2. Create Procedure
  3. Grant Permission


Which is the best practice to follow for SP Deployment from DBA Stand point?
Database Developer
Commented:
If you don't have SQL Server 2016 or later, use:
IF OBJECT_ID('dbo.procname') IS NULL
BEGIN
	DECLARE @SqlStmt NVARCHAR(MAX)
	SET @SqlStmt = 'CREATE PROCEDURE dbo.procname AS
		BEGIN
			SELECT NULL
		END'
	EXECUTE sp_executesql @SqlStmt

ALTER PROCEDURE dbo.procname …

Open in new window


this allows SQL to not have to drop and recreate, thus generating a new object_id, and you having to grant the permissions again.

For 2016 and above, you can use the syntax: CREATE OR ALTER PROCEDURE dbo.procname
chokkaStudent

Author

Commented:
Thanks
Doug BishopDatabase Developer

Commented:
no problem. Thank you.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial