How to make your Stored Procedures reentrant, or in a format that can be executed any number of times on any database.

If your SQL development is like mine, then delivering your Stored Procedures can require some special formatting.

The company I am privileged to work for has many more than 1,000 live customers, each with their own SQL Server.  So when I write a Stored Procedure I need to produce it in a format for a brand new install and for an upgrade.

This upgrade process can be tricky.  So the method that we have developed will require that my SQL run on a database that has a copy of my Stored Procedure already and a database without it.  This means that I need to start my Stored Procedure with CREATE or ALTER, but which do I choose?

You may now be thinking, "why not just drop the old (if it exists) and always perform a CREATE?"  Well, the customer may have altered permissions on the Stored Procedure that need to be preserved.  Retrieving, saving and reestablishing these permissions can get complex very quickly.  Especially since I have some SQL servers that I am not given enough RIGHTS to do that.

So one easy method that I have used for a number of years is to submit all my Stored Procedure work beginning like this:

IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'NewSP')
EXEC ('CREATE PROC NewSP AS SELECT ''Mini SP Version.''')
GO

ALTER Procedure NewSP

AS

select 'Full SP SQL Code' as [My wonderful SQL Code]

GO

IF EXISTS (SELECT 1 FROM sys.database_principals WHERE [name] = N'My_DB_User1')

Grant Execute on NewSP to My_DB_User1

GO

IF EXISTS (SELECT * FROM sys.schemas WHERE [name] = N'My_DB_Schema')

Grant Execute on NewSP to My_DB_Schema

GO

(c) 2014 RosSQL, all rights reserved.  This publication may not be republished without the expressed written permission of RosSQL.