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

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?
chokkaStudentAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Pratik SomaiyaApplication Development AssociateCommented:
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 AssociateCommented:
whenever you DROP and CREATE you may lose the permissions associated with that object.
SStoryCommented:
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?
5 Ways Acronis Skyrockets Your Data Protection

Risks to data security are risks to business continuity. Businesses need to know what these risks look like – and where they can turn for help.
Check our newest E-Book and learn how you can differentiate your data protection business with advanced cloud solutions Acronis delivers

Download Free Ebook
chokkaStudentAuthor 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?
Doug BishopDatabase DeveloperCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
chokkaStudentAuthor Commented:
Thanks
Doug BishopDatabase DeveloperCommented:
no problem. Thank you.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.