{"id":98103,"date":"2023-08-25T21:41:42","date_gmt":"2023-08-25T21:41:42","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=98103"},"modified":"2023-09-01T18:48:41","modified_gmt":"2023-09-01T18:48:41","slug":"yet-another-reason-to-not-use-sp_-in-your-sql-server-object-names","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/blogs\/yet-another-reason-to-not-use-sp_-in-your-sql-server-object-names\/","title":{"rendered":"Yet Another Reason to Not Use sp_ in SQL Server Object Names"},"content":{"rendered":"

In 2012, Aaron Bertrand<\/a> said most everything I knew<\/a> (and a bit more) about the issues with using the sp_<\/code> prefix. Procedures prefixed with sp_<\/code> 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.<\/p>\n

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<\/code> procedure so you can run the backup command from any database.<\/p>\n

But if you don’t need the special properties of the sp_procedure<\/code>, 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<\/code> behaves differently than CREATE<\/code> in a way that was really confusing to me as I was working on a piece of code today.<\/p>\n

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

I had accidentally executed the procedure create script in the master<\/code> database. (I know, I am the only person with this mistake to their name. But if there is no USE<\/code> statement to start off a script, when I am testing out code it often ends in master. I don’t have access to ANY<\/code> 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<\/code>.)<\/p>\n

Using CREATE OR ALTER<\/h2>\n

So I executed something like the following:<\/p>\n

USE master; --I clearly didn't have a USE \r\n            --statement in my code!\r\nGO\r\nCREATE OR ALTER PROCEDURE dbo.sp_DoSomethingSimple\r\nAS\r\n BEGIN\r\n\t--format for easier access in writing\r\n\tSELECT CAST(DB_NAME() AS VARCHAR(30)) AS DatabaseName;\r\n END;\r\nGO<\/pre>\n
Then, later in my testing, I did something like this:<\/p>\n
USE WideWorldImporters\r\nGO\r\nEXECUTE  dbo.sp_DoSomethingSimple;\r\nGO<\/pre>\n
This worked fine and returned:<\/p>\n
DatabaseName\r\n------------------------------\r\nWideWorldImporters<\/pre>\n
Seemed fine. So, I went to drop and recreate this procedure with a new column in the output.<\/p>\n
CREATE OR ALTER PROCEDURE dbo.sp_DoSomethingSimple\r\nAS\r\n BEGIN\r\n     SELECT CAST(DB_NAME() AS VARCHAR(30)) AS DatabaseName,\r\n           CAST(USER_NAME () AS VARCHAR(30)) AS UserName;\r\n END;\r\nGO<\/pre>\n
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:<\/p>\n
Msg 208, Level 16, State 6, Procedure sp_DoSomethingSimple, Line 1 [Batch Start Line 34]<\/code><\/p>\n
Invalid object name 'dbo.sp_DoSomethingSimple'.<\/code><\/p>\n
Well, I never. I just executed this procedure, and it was there. And EVEN IF IT WEREN’T<\/strong>, I said CREATE OR ALTER<\/code>. 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<\/code> database. So, I cleared it out and all was okay.<\/p>\n
But for sake of demonstration, let’s leave that object right where it was. In the master<\/code> database. This code will make sure that the procedure is only in master, not in your current database:<\/p>\n
SELECT  'master', CONCAT(OBJECT_SCHEMA_NAME(object_id),\r\n\t\t'.',OBJECT_NAME(object_id))\r\nFROM    master.sys.procedures\r\nWHERE   name = 'sp_DoSomethingSimple'\r\nUNION \r\nSELECT  CAST(DB_NAME() AS NVARCHAR(30))\r\n\t\t, CONCAT(OBJECT_SCHEMA_NAME(object_id),\r\n\t\t'.',OBJECT_NAME(object_id))\r\nFROM    sys.procedures\r\nWHERE   name = 'sp_DoSomethingSimple'\r\nGO<\/pre>\n
\/*<\/p>\n
This should only return:<\/p>\n
                               \r\n------------------------------ ----------------------------\r\nmaster                         dbo.sp_DoSomethingSimple<\/pre>\n
If it just has the one row for master<\/code>, we can continue on.<\/p>\n
Ok, so let’s do far more dangerous version of this. Let’s try to drop the procedure. CREATE OR ALTER<\/code> didn’t change anything, so other than confusing me, not a big deal. But what about:<\/p>\n
USE WideWorldImporters\r\nGO\r\nDROP PROCEDURE dbo.sp_DoSomethingSimple;\r\nGO\r\nEXECUTE  dbo.sp_DoSomethingSimple;\r\nGO<\/pre>\n
Uh oh. I have just silently dropped the master procedure that I really didn’t want to lose.<\/p>\n
Msg 2812, Level 16, State 62, Line 77\r\nCould not find stored procedure 'dbo.sp_DoSomethingSimple'. <\/pre>\n
Of course, I can create the procedure in the WideWorldImporters<\/code> 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_<\/code> as the prefix, unless that was you too…<\/p>\n
Using CREATE and DROP<\/h2>\n
Finally, what if instead of CREATE OR ALTER<\/code>, you had just used CREATE<\/code>? Assuming you have been following along, there should not be a sp_DoSomethingSimple<\/code> in either place now, but I added code to make sure:<\/p>\n
USE master; \r\nDROP PROCEDURE IF EXISTS dbo.sp_DoSomethingSimple;\r\nGO\r\nUSE WideWorldImporters;\r\nDROP PROCEDURE IF EXISTS dbo.sp_DoSomethingSimple;\r\nGO<\/pre>\n
After dropping the procedures, try executing the following:<\/p>\n
USE master; \r\nGO\r\nCREATE PROCEDURE dbo.sp_DoSomethingSimple\r\nAS\r\n BEGIN\r\n\t--format for easier access in writing\r\n\tSELECT CAST(DB_NAME() AS VARCHAR(30)) AS DatabaseName;\r\n END;\r\nGO\r\nUSE WideWorldImporters\r\nGO\r\nCREATE PROCEDURE dbo.sp_DoSomethingSimple\r\nAS\r\n BEGIN\r\n\tSELECT CAST(DB_NAME() AS VARCHAR(30)) AS DatabaseName,\r\n\t\t   CAST(USER_NAME () AS VARCHAR(30)) AS UserName;\r\n END;\r\n GO<\/pre>\n
If the procedures did not exist, no error occurred. If that wasn’t fun enough to say “no sp_<\/code> procedures”, then I have one more reminder:<\/p>\n
Use WideWorldImporters;\r\nDROP PROCEDURE dbo.sp_DoSomethingSimple;<\/pre>\n
Returns the following message:<\/p>\n
Commands completed successfully.<\/code><\/p>\n
Run it again:<\/p>\n
DROP PROCEDURE dbo.sp_DoSomethingSimple;\/*<\/pre>\n
Same return message, you just dropped the one in master<\/code>. But what you thought happened was that the first DROP PROCEDURE<\/code> failed. Or you probably did, I know that was my first reaction. A third execution will get you the message you expected:<\/p>\n
Msg 3701, Level 11, State 5, Line 126<\/code><\/p>\n
Cannot drop the procedure 'dbo.sp_DoSomethingSimple', because it does not exist or you do not have permission.<\/code><\/p>\n
Changing the syntax to DROP PROCEDURE IF EXISTS<\/code> will not change the outcome of the following batches. The second execution will still drop the master<\/code> copy. If you use the sort of code we used before IF EXISTS<\/code> existed:<\/p>\n
IF EXISTS (SELECT * \r\n           FROM sys.objects \r\n           WHERE OBJECT_ID('dbo.sp_DoSomethingSimple') = \r\n                                                 object_id)\r\nDROP PROCEDURE dbo.sp_DoSomethingSimple;<\/pre>\n
Then it would not drop the master copy (but to use a cumbersome prefix like sp_<\/code>, is it worth not being able to say DROP PROCEDURE IF EXISTS<\/code>?)<\/p>\n
Conclusion<\/h2>\n
Only use sp_<\/code> 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.<\/p>\n
 <\/p>\n","protected":false},"excerpt":{"rendered":"
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……<\/p>\n","protected":false},"author":56085,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[2,143531],"tags":[],"coauthors":[19684],"class_list":["post-98103","post","type-post","status-publish","format-standard","hentry","category-blogs","category-t-sql-programming-sql-server"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/98103","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/56085"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=98103"}],"version-history":[{"count":2,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/98103\/revisions"}],"predecessor-version":[{"id":98185,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/98103\/revisions\/98185"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=98103"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=98103"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=98103"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=98103"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}