When writing a script to change a piece of code, say a stored procedure or view, there are 3 basic options. I'll start at the outset by saying I use all 3 and that they each have their pluses and minuses. I'm not trying to advocate one or another, just describe each so you can make an informed decision which to use. An important thing to remember that affects all of these methods is the fact that CREATE and ALTER statements have to be the first statement in a batch.
GO -- Required so that the CREATE is the first command of a batch.
Upsides: Simple to code and maintain. It doesn't matter if the object does or does not exist.
Downsides: Any specific permissions to the object are going to be lost. By specific permissions I mean granting permissions to the object, not to the schema or database. This happens because when the object is dropped and re-created it is assigned a new object id. This object id is used for references between the various system tables/views. This won't affect code that refers to the object because those references are done by name which of course isn't changing.
Next: ALTER. Alter is the simplest way to change the object. In this method you just "alter" the code.
Upsides: This is the simplest of the three methods, although admittedly not by much. Any specific permissions to the object are maintained. See above for why.
Downsides: Only works if the object already exists. This means that this method may not work with automated processes used to push code onto an instance.
So how do you do it? Dynamic SQL of course.
Upsides: This is the most flexible method. The object is created when it needs to be and altered if it already exists. This means that if it already exists any changes won't affect existing permissions, and yet it can still be run when the code doesn't exist yet and not give an error.
Downsides: This is by far the most complicated method to implement and to maintain. I'm not going to go into any detail as to why. If you've ever done anything more than basic dynamic SQL you already know. If you haven't you'll find out.
No dynamic SQL AND no loss of permissions or metadata about when the procedure was created, etc.AND if the new definition fails to compile for whatever reason the old one, if there was one, remains in place!
Not a bad idea but you will need to make a minor change. You can't put the CREATE PROCEDURE after the IF. You could however use dynamic SQL.
Yep, that's pretty much how I actually do it. I got careless genericizing the example.
I also add a print statement that echoes out the sql string plus the DB_NAME() just for sanity checking that it got created in the correct database since I work on several daily. And of course lots of little changes if you're creating a function instead of a procedure.
Also you might try using OBJECT_ID(objectname) IS NOT NULL instead of exists. I don't know that it's any faster but it's certainly shorter.
Here is a test I did to be sure I remembered it correctly.
For SQL Server I use the object existance check to CREATE a "stub" object if it doesn't exist and then have alll scripts use an ALTER statement.
If I'm using Oracle, I use the CREATE OR REPLACE statement. It's not ANSI, but it's very handy and is one of the few places where Oracle has it over SQL Server.
I have to say I really like the "stub" idea. One of the best parts of blogging for me is the new things I learn from the comments. And yes CREATE OR REPLACE would be really nice to have!
Another Pro vs Con of these approaches is that,with the DROP/CREATE option there is a small window of time where the script does not exist in the DB between the DROP and the CREATE and thus running programs could fail. Whereas with ALTER, it is transactional, and any running code would wait during the change to the script because the script object is locked during the change. Thus as long as the change does not affect the applicaiton (argument changes for example) it will not have any impact on running programs.
Excellent point, and one I should have remembered. It actually happened to me at one job where the stored procedure I was working on was being called multiple times a second. There was just long enough between the DROP and CREATE for the sp to show up missing a couple of times.
I get the concept of the alter. However, in the examples above, if the procedure does not exist, creating a stub and then later in the script altering doesn't really address the full issue. I assume that the stub would also have to have scripting to set the correct permissions, otherwise what is gained by the alter later, just symmetry in coding practices.
We have policy that when 'releases' are pushed out all procedures are dropped then created and permissions assigned. These pushes are run when the DBA is aware that no users are on the system (maintenance window). If we are pushing out a bug fix or immediate fix, we then use the alter to prevent interrupting users.
Here is a for instance for you. I have written a SSIS package that goes out to each of the servers my team maintains and collects information. Part of how this is managed is using stored procedures. The way I handle updates to the code currently is the DROP and CREATE method. The first thing the process does is "refresh" the code by dropping the SP and re-creating it to make sure that the server has the latest copy. I could certainly use ALTER's instead, however, by doing it this way when I want to add a new instance to the process all I have to do is add it to my control table and the code is placed on the server automatically.
Which get's us to the stub idea. Using that method I can do ALTER's instead of CREATE's by creating a stub at the beginning then always doing an ALTER. That way on existing instances I'm just doing the ALTER and on new ones I'm creating a stub then doing the ALTER.
We use both at my current employer, but one thing to highlight is that whichever approach you take you should always have your object level grant statements as part of the script, so that shouldn't be an issue with the create+drop approach. I wouldn't want to depend on inheriting permissions through alter.
I would think that adding the object level grant's to the scripts could add a large amount of maintenance work. I can create a script to add a dozen or so permissions in a minute or so. Then having to remember to add those permissions to a series of scripts, or re-scripting the objects would be a lot of extra effort. Also if someone forgets to remove something from one of the scripts after removing a permission you could end up with some security issues.
Now on the other hand if you have some sort of automated scripting happening then you can probably ignore everything I said above.
I'm assuming your scripts are in some sort of source control. Because they should be :>.
I've done a couple of posts on writing dynamic SQL (which is what you are doing).
If those don't help then feel free to comment again or email me and I'll see if I can't help you more.
I run this to check to see if this sp exists in sqlserver 2012. It does not.
But when I run the script to create the SP it always changes CREATE to ALTER and that fails because the sp doesn't exist?
— The type of the field has 'int' in it.
— The type of the field has 'numeric' in it.
ELSE — The rest case of the field type is considered as char type.
I don't really understand. I see that you are checking for the SP in the initial query then trying to create it. What is changing the CREATE to an ALTER? Is this part of an automated process?
Obviously I over my head in this.
The programmer we had on a different server created this sp and it is used in his code. That was sqlserver 2008. He was a genius. Now we have moved the code to a new server and it uses sqlserver 2012. The code part that references this sp fails. So I was trying to create the sp a new on the new server.
After I delete the sp, before I execute the script again I run the check to see if it exists.
When I copy and paste the sp script into a query window and execute it, The Create is changed to ALTER when I open the sp. And the name of the sp following the ALTER command is underlined in red.
It's no problem. And you aren't really having one either 🙂 Once you run the create script the sp is created. You are then opening the sp in an "edit" mode (I'm being very lose with terminology here). The reason for the ALTER is that SQL is setting it up so you can make changes to the now already existing sp. You might double check that there isn't another version of this SP under a different schema name (the dbo in front is the schema) but I doubt it.
I'm commenting here (even though the post is quite old) to share what we've been doing with this.
We encountered a situation where we needed to update a remote site, but we didn't have DROP permissions. For ever, we have been using the 'DROP and CREATE' script built into SSMS 2008 R2, but now we needed to change.
We need that Public receives EXECUTE permissions on these, so we script that into each CREATE. After that, the ALTER doesn't change it, and if they add or modify the permissions, they remain. Doing it this way, it's an easy task to copy the name of the function or stored procedure, and use the Template Parameter replacement to automating the completion of these scriptlets.
I'm not a big fan of granting anything to public but beyond that you might look up a bit in the comments. The IF NOT EXISTS .. CREATE stub .. ALTER works really well.
Additionally, as we need to do this for functions, I hadn't found in one place how to create a minimalist header for scalar functions and table functions. Of course, you would only insert the header you need, not all three of them.
However, if there are issues with how we're now doing it, I'd love to know!
No, you are right. I wan't looking closely enough 🙂 It is basically the same thing.