Yet Another Reason to Not Use sp_ in SQL Server Object Names

Comments 0

Share to social media

In 2012, Aaron Bertrand said most everything I knew (and a bit more) about the issues with using the sp_ prefix. Procedures prefixed with sp_ have special powers when placed in the the master database in that it can be executed anywhere on the server after that. Nothing much has change in those suggestions.

It isn’t that such objects are to be completely avoided, it is that they are ONLY to be used when you need the special qualities. Ola Hallengren’s backup solution creates a dbo.sp_BackupServer procedure so you can run the backup command from any database.

But if you don’t need the special properties of the sp_procedure, they are bad for the reasons Aaron stated, the reason I stumbled upon today being just a special subset. In this case CREATE OR ALTER behaves differently than CREATE in a way that was really confusing to me as I was working on a piece of code today.

My problems are going to be simple code management issues where some code existed in the master database and it confused me as to why something was working, and then why it wasn’t.)

I had accidentally executed the procedure create script in the master database. (I know, I am the only person with this mistake to their name. But if there is no USE statement to start off a script, when I am testing out code it often ends in master. I don’t have access to ANY production resources, so I am usually playing with other people’s code. It is in fact a good reason to change your default database to tempdb.)

Using CREATE OR ALTER

So I executed something like the following:

Then, later in my testing, I did something like this:

This worked fine and returned:

Seemed fine. So, I went to drop and recreate this procedure with a new column in the output.

This is where it gets weird, and where your developer is going to be confused… quite confused. It said it did not exist, even though I just executed it:

Msg 208, Level 16, State 6, Procedure sp_DoSomethingSimple, Line 1 [Batch Start Line 34]

Invalid object name 'dbo.sp_DoSomethingSimple'.

Well, I never. I just executed this procedure, and it was there. And EVEN IF IT WEREN’T, I said CREATE OR ALTER. So, create it. A few more rounds like this, a stop for a snack and maybe fight a few Goombas on Mario Brothers, and then a few more rounds against the query compiler… it hit me. I bet I saved this in the master database. So, I cleared it out and all was okay.

But for sake of demonstration, let’s leave that object right where it was. In the master database. This code will make sure that the procedure is only in master, not in your current database:

/*

This should only return:

If it just has the one row for master, we can continue on.

Ok, so let’s do far more dangerous version of this. Let’s try to drop the procedure. CREATE OR ALTER didn’t change anything, so other than confusing me, not a big deal. But what about:

Uh oh. I have just silently dropped the master procedure that I really didn’t want to lose.

Of course, I can create the procedure in the WideWorldImporters database now, but it is only available to my database. If that is what you wanted, then that is fine, but if not, you will eventually hear about it. Hopefully you won’t have to admit it was your fault, but if it is, blame the person who used sp_ as the prefix, unless that was you too…

Using CREATE and DROP

Finally, what if instead of CREATE OR ALTER, you had just used CREATE? Assuming you have been following along, there should not be a sp_DoSomethingSimple in either place now, but I added code to make sure:

After dropping the procedures, try executing the following:

If the procedures did not exist, no error occurred. If that wasn’t fun enough to say “no sp_ procedures”, then I have one more reminder:

Returns the following message:

Commands completed successfully.

Run it again:

Same return message, you just dropped the one in master. But what you thought happened was that the first DROP PROCEDURE failed. Or you probably did, I know that was my first reaction. A third execution will get you the message you expected:

Msg 3701, Level 11, State 5, Line 126

Cannot drop the procedure 'dbo.sp_DoSomethingSimple', because it does not exist or you do not have permission.

Changing the syntax to DROP PROCEDURE IF EXISTS will not change the outcome of the following batches. The second execution will still drop the master copy. If you use the sort of code we used before IF EXISTS existed:

Then it would not drop the master copy (but to use a cumbersome prefix like sp_, is it worth not being able to say DROP PROCEDURE IF EXISTS?)

Conclusion

Only use sp_ as a prefix to your procedure if you need it, and then it goes in the master database. Otherwise, you may get pretty confused one day when a system object stops working because it doesn’t always work like you expect.

 

Load comments

About the author

Louis Davidson

See Profile

Louis is the editor of this Simple-Talk website. Prior to that, has was a corporate database developer and data architect for a non-profit organization for 25 years! Louis has been a Microsoft MVP since 2004, and is the author of a series of SQL Server Database Design books, most recently Pro SQL Server Relational Database Design and Implementation.

Louis's contributions