Compare script output, feature for if not exists checks.

hinsona

I generate one update script from a baseline server, then use the same script against multiple server targets. The issue I've encountered, is that if one server is in a slightly different state, even though the procedure in question is in the script  it doesn't create missing procedures when using object exists checks. Also, when doing this it's unclear if the script succeeded, because it conditionally skips it in the case that it's missing.

What I'd like for it to do is...

With object exists checks, provide a way to do IF NOT EXISTS create a procedure/function stub with an empty body. Follow that with the actual method body in an ALTER statement rather than CREATE. This works whether the procedure/function in question exists or not on the server. Permissions can follow the creation of the stub, since alter statements do not change permissions.

It might look something like this...

IF OBJECT_ID('[dbo].[myProc]') IS NULL
BEGIN
  EXEC sp_executesql N'CREATE PROCEDURE [dbo].[myProc] AS BEGIN RETURN; END'
  PRINT N'Adding procedure  [dbo].[myProc]'
  EXEC sp_executesql N'GRANT EXECUTE ON [dbo].[myProc] TO [myUser]'
  PRINT N'Altering permissions on [dbo].[myProc]'
END;
ELSE
  PRINT N'Updating procedure [dbo].[myProc]'
GO
EXEC sp_executesql N'
ALTER PROCEDURE [dbo].[myProc]
(@MyParam VARCHAR(50))
AS
BEGIN
  PRINT @MyParam;
END;
'
GO

Currently, I have been modifying the script output but was wondering if it's possible for the script generation to do it.

Sergio R

I think that "DROP and CREATE" option can help you achieve that: https://documentation.red-gate.com/sc/setting-up-the-comparison/setting-project-options#Settingprojectoptions-AddDROPandCREATEforrerunnablescripts

hinsona

Sergio R said:

I think that "DROP and CREATE" option can help you achieve that: https://documentation.red-gate.com/sc/setting-up-the-comparison/setting-project-options#Settingprojectoptions-AddDROPandCREATEforrerunnablescripts

I am aware of this, and the use of it results in the unnecessary dropping of all procedures affected by the script. It also means that permissions must be applied again for everything as well. It's not ideal to do unnecessary work in my opinion.

Sergio R

I understand, I'm afraid that DROP and CREATE is the closest option available currently in SQL Compare.
If you wish to, you can suggest that as an improvement on our Uservoice site at https://redgate.uservoice.com/forums/141379-sql-compare