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:
|
1 2 3 4 5 6 7 8 9 10 |
USE master; --I clearly didn't have a USE --statement in my code! GO CREATE OR ALTER PROCEDURE dbo.sp_DoSomethingSimple AS BEGIN --format for easier access in writing SELECT CAST(DB_NAME() AS VARCHAR(30)) AS DatabaseName; END; GO |
Then, later in my testing, I did something like this:
|
1 2 3 4 |
USE WideWorldImporters GO EXECUTE dbo.sp_DoSomethingSimple; GO |
This worked fine and returned:
|
1 2 3 |
DatabaseName ------------------------------ WideWorldImporters |
Seemed fine. So, I went to drop and recreate this procedure with a new column in the output.
|
1 2 3 4 5 6 7 |
CREATE OR ALTER PROCEDURE dbo.sp_DoSomethingSimple AS BEGIN SELECT CAST(DB_NAME() AS VARCHAR(30)) AS DatabaseName, CAST(USER_NAME () AS VARCHAR(30)) AS UserName; END; GO |
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:
|
1 2 3 4 5 6 7 8 9 10 11 |
SELECT 'master', CONCAT(OBJECT_SCHEMA_NAME(object_id), '.',OBJECT_NAME(object_id)) FROM master.sys.procedures WHERE name = 'sp_DoSomethingSimple' UNION SELECT CAST(DB_NAME() AS NVARCHAR(30)) , CONCAT(OBJECT_SCHEMA_NAME(object_id), '.',OBJECT_NAME(object_id)) FROM sys.procedures WHERE name = 'sp_DoSomethingSimple' GO |
/*
This should only return:
|
1 2 |
------------------------------ ---------------------------- master dbo.sp_DoSomethingSimple |
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:
|
1 2 3 4 5 6 |
USE WideWorldImporters GO DROP PROCEDURE dbo.sp_DoSomethingSimple; GO EXECUTE dbo.sp_DoSomethingSimple; GO |
Uh oh. I have just silently dropped the master procedure that I really didn’t want to lose.
|
1 2 |
Msg 2812, Level 16, State 62, Line 77 Could not find stored procedure 'dbo.sp_DoSomethingSimple'. |
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:
|
1 2 3 4 5 6 |
USE master; DROP PROCEDURE IF EXISTS dbo.sp_DoSomethingSimple; GO USE WideWorldImporters; DROP PROCEDURE IF EXISTS dbo.sp_DoSomethingSimple; GO |
After dropping the procedures, try executing the following:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
USE master; GO CREATE PROCEDURE dbo.sp_DoSomethingSimple AS BEGIN --format for easier access in writing SELECT CAST(DB_NAME() AS VARCHAR(30)) AS DatabaseName; END; GO USE WideWorldImporters GO CREATE PROCEDURE dbo.sp_DoSomethingSimple AS BEGIN SELECT CAST(DB_NAME() AS VARCHAR(30)) AS DatabaseName, CAST(USER_NAME () AS VARCHAR(30)) AS UserName; END; GO |
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:
|
1 2 |
Use WideWorldImporters; DROP PROCEDURE dbo.sp_DoSomethingSimple; |
Returns the following message:
Commands completed successfully.
Run it again:
|
1 |
DROP PROCEDURE dbo.sp_DoSomethingSimple;/* |
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:
|
1 2 3 4 5 |
IF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID('dbo.sp_DoSomethingSimple') = object_id) DROP PROCEDURE dbo.sp_DoSomethingSimple; |
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