How can we help you today? How can we help you today?

Compare script output, feature for if not exists checks.

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.
hinsona
0

Comments

3 comments

Sort by

Add comment

Please sign in to leave a comment.