Oracle has great syntax to CREATE OR REPLACE a stored procedure.
When I have to sync up multiple servers or deploy a specific procedure across my entire SQL Server Environment, I like to have a single script which can create the procedure where it needs to be created, and alter the procedure if it needs to be altered.
To make my code runnable on a server that may / may not already have my stored procedure I create a code stub, then replace it with the correct code.
Here is an example of code I use for database backups with Red Gate
IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME LIKE 'usp_BackupLogs' AND ROUTINE_TYPE LIKE 'PROCEDURE' )
BEGIN
EXEC sp_executesql N'CREATE PROCEDURE [dbo].[usp_BackupLogs] AS SELECT CONVERT(VARCHAR(20),getdate() ,120) + '' - Code Stub to Be Replaced by Alter Script '' as [Stub]'
END
GO
ALTER PROCEDURE [dbo].[usp_BackupLogs]
AS
DECLARE @emailMessage NVARCHAR(4000)
DECLARE @exitcode int
DECLARE @sqlerrorcode int
EXEC MASTER..sqlbackup N'-SQL "BACKUP LOGS [*] TO DISK = ''\\BACKUPS\LOGS\'' WITH NAME = '''', DESCRIPTION = '''', INIT, PASSWORD = ''mypassword'', MAXDATABLOCK = 65536,KEYSIZE = 256, COMPRESSION = 1, THREADS = 1"', @exitcode OUTPUT, @sqlerrorcode OUTPUT
IF (@exitcode <> 0) OR (@sqlerrorcode <> 0)
BEGIN
SET @emailMessage = 'FAILED LOG BACKUP | ' + @@SERVERNAME + ' | ' + SUSER_SNAME() + ' | '+ DB_NAME() + ' | ' + CONVERT(VARCHAR(20),getdate(),120)
EXEC msdb.dbo.sp_send_dbmail
@recipients=N'DBAS@mydomain.com',
@importance = 'High',
@subject = @emailMessage,
@body= @emailMessage
END
-- COMMENTED OUT INTENTIONALLY - DO NOT WANT EMAILS ON SUCCESSFUL LOG BACKUPS (esp. if scheduled every 15 minutes/ frequently)
--ELSE
-- BEGIN
-- SET @emailMessage = 'SUCCESSFUL LOG BACKUP | ' + @@SERVERNAME + ' | ' + SUSER_SNAME() + ' | '+ DB_NAME() + ' | ' + CONVERT(VARCHAR(20),getdate(),120)
-- EXEC msdb.dbo.sp_send_dbmail
-- @recipients=N'DBAS@mydomain.com',
-- @subject = @emailMessage,
-- @body= @emailMessage ;
-- End
Following this example if I want to deploy all the code from a server to another machine, I generate the CREATE Statements using the following code:
This prevents the need for 2 separate scripts (CREATE AND ALTER).
----------------------------------------------------------------
-- CREATE OR REPLACE ALL STORED PROCEDURES
----------------------------------------------------------------
SELECT
ROUTINE_NAME
,'EXEC sp_executesql N'''+REPLACE(N'CREATE PROCEDURE ['+ROUTINE_SCHEMA+'].['+ROUTINE_NAME+'] AS SELECT CONVERT(VARCHAR(20),getdate() ,120) + '' - Code Stub to Be Replaced by Alter Script '' as [Stub]','''','''''') + '''' as cmd
FROM INFORMATION_SCHEMA.ROUTINES
WHERE
ROUTINE_TYPE LIKE 'PROCEDURE'
ORDER BY ROUTINE_NAME ASC
Subsequently I script all objects as ALTERS and append to the CREATE script.
I create a single Script File with the following convention
1. CREATE scripts (code stubs) ( with if not exists )
2. ALTER scripts (real code)
