T-SQL Tuesday is a monthly SQL Server blogging event. One of us sets a topic for the month’s event, and we all have a week in which to write a blog post that fits that topic. This month’s event is hosted by Brad Schulz (blog), and he’s chosen the subject of Procedures & Functions.
A Quick Maintenance / Implementation Tip
A quick tip for something to do when building release scripts.
If you’re not sure about whether an object exists, and need to make sure that the right version is provided, then your release script might start out with a test to see if the object exists, and to drop it if it does, then create the replacement:
IF NOT (OBJECT_ID('dbo.foo') IS NULL) THEN DROP PROCEDURE foo END
CREATE PROCEDURE foo ...
This isn’t particularly friendly to running applications, may well cause issues with dependent objects, and you’ll have to be very careful to get the permissions right.
However, if you check to see if the object exists, and if it doesn’t then create a stub which can then be modified by your release script, these issues (mostly) go away.
IF OBJECT_ID('dbo.foo') IS NULL THEN
EXEC ('CREATE PROCEDURE dbo.foo AS RETURN 0')
ALTER PROCEDURE dbo.foo ...
OK, so it’s a bit more effort, but it does mean that you won’t lose permissions information. And the headaches that fixing that sort of thing can cause makes it well worth the effort.
Of course, for bonus points, you should put the
GRANT statement at the end of your release script anyway, just in case you are creating the object from scratch…