If writing code is the meat of our work then then packaging our stored procedures for deployment is the bread and butter to complete the sandwich. Deploying them is key so here we’ll be looking at options for how to effectively script those changes.
The specific challenge we’re looking at here is how to change the contents of a stored procedure when you don’t know what version if any is already deployed across your environments.
If your team is tight with version control and incremental changes then this may be part of your workflow but for those of us working across teams or with different coding styles you can find use in these different approaches to bring consistency to changes.
Alter
When we’re making changes to a procedure it’s fair to assume that the procedure is already deployed and in use. If that’s the case then we can simply script up an ALTER statement right?
ALTER PROCEDURE dbo.DeployMe AS
BEGIN
/* Do stuff */
END
GO
You see, the crux of this comes down to the question of if the procedure has already been correctly deployed to the environment which you’re targeting.
It isn’t uncommon to find code which has been deployed into development (or even test) environments which hasn’t made it to a production environment. Let’s look at an option to combat this.
Drop and create
To avoid any issues with the code potentially existing we’ll just drop whatever may be there and recreate the procedure from scratch.
We’ll be careful with our DROP statement though as we aren’t completely sure it exists so we’ll perform a check on the system catalog table first:
IF EXISTS (
SELECT 'exists'
FROM sys.procedures
WHERE object_id = OBJECT_ID('dbo.DeployMe'))
BEGIN
DROP PROCEDURE dbo.DeployMe;
END
GO
CREATE PROCEDURE dbo.DeployMe AS
BEGIN
/* Do Stuff */
END
GO
That’s much safer for us, we can be pretty confident in this deployment for either a new or existing procedure.
There is, however, a but…
But there will be a period where a potentially existing – and in use – procedure may be dropped from your environment which could fail any dependant code. That’s not too ideal but could be acceptable depending on your workloads.
However the more concerning part of this approach would be if the CREATE portion of the change failed. If this happened then you’d be without any procedure at all which is not a good place to be in. We may want to consider another approach.
Before we move on though, a little bonus. If you’re running a more recent version of SQL Server (2016 upwards) you can condense the DROP portion of this script down into the much more succinct version below:
DROP PROCEDURE IF EXISTS dbo.DeployMe;
GO
CREATE PROCEDURE dbo.DeployMe AS
BEGIN
/* Do Stuff */
END
GO
Use a stub procedure
The last approach wasn’t bad but by needing to recreate the procedure we left ourselves vulnerable to a failed deployment. This time we’ll flip that on it’s head and instead of trying to DROP and then CREATE we’ll now try to CREATE and then ALTER.
The idea here is that if the procedure doesn’t exist we’ll CREATE an empty stub, followed by an ALTER which will proceed whether the existing procedure is a prior version or our empty stub. It’ll look a little like this:
IF NOT EXISTS (
SELECT 'exists'
FROM sys.procedures
WHERE object_id = OBJECT_ID('dbo.DeployMe'))
BEGIN
EXEC ('CREATE PROCEDURE dbo.DeployMe AS RETURN;');
END
GO
ALTER PROCEDURE dbo.DeployMe AS
BEGIN
/* Do Stuff */
END
GO
Now we’re getting somewhere!
Using this approach we’ll be able to create a new copy of the procedure as well as changing an existing copy. This won’t have the issue of leaving us with no procedure if our ALTER fails too so a much better situation.
So the last question, can we do it without being so verbose? Yes we can!
Create or alter
If you’re on a relatively modern version of SQL Server (2016 SP1 or higher) then you’ll have access to the extremely useful CREATE OR ALTER syntax. If you haven’t seen this before then you’ll be pleasantly surprised.
We can use this syntax to combine both CREATE and ALTER statements within a single command. This can leave your procedures almost unchanged from the regular ALTER we started with, with only a minor change to the definition.
Let’s see it in action:
CREATE OR ALTER PROCEDURE dbo.DeployMe AS
BEGIN
/* Do Stuff */
END
GO
How easy is that?
This quite simply tops the lot. It’s a super trivial to change in our code, it protects us from procedures which haven’t yet been deployed, and will also leave an existing version in place if there’s any issues with syntax or references in the new procedure.
I think this is firmly my favourite solution for deploying changes to procedures.
Wrap up
In this post we’ve looked at different options for scripting up changes to stored procedures to allow them to be deployed effectively in any environment. Below is a summary of the options:
ALTERis standard but can lead to issues when a procedure doesn’t already existDROPandCREATEwill protect us from a procedure not existing but may leave us with no proc if theCREATEfails for any reason- Using a stub approach resolves the previous issues as we’ll always have a stub created to
ALTERagainst CREATE OR ALTERagain resolves the issues but is a very tiny change compared to creating a stub in the code
Whilst the first two approaches are feasible in environments where change control and deployments are closely monitored or orchestrated these aren’t always the best approach for more volatile environments such as development instances.
The latter two approaches both achieve the same aims of allowing more consistent and successful deployments without risks of leaving the code in a worse state than when we started.
Both of these are popular options with my own personal slant being towards the newer method with less code required. With that said there are many who favour a stub procedure as the code is compatible with a broader range of SQL Server versions.
Another takeaway from this should be that if you’re not running 2016 or alter you’ll also be missing out on some lovely time-saving commands (not to mention Columnstore, for another time).
Do you have any particularly stories from absolutely dreadful deployments caused by issues like this? Why not share and commiserate each other on painful days we’ve (hopefully) left behind.
Andy Brownsword 