An interesting post recently from Chad Callihan on the reasons why someone shouldn’t use sp_ to prefix their stored procedures. This is advice that I’ve seen for years, but I haven’t run into this lately with many customers. It seems I do see some usp_, but not sp_. I think that’s good, and if you’re not sure why, read Chad’s post.
I think my main reason for not doing this is that I sometimes create procs in master. Not because it’s a good idea (it isn’t) or I want to, but because I’ll make mistakes. I’ll accidentally connect to master and deploy a script, because I’m not paying enough attention. Usually, I’ll catch this right away, but if not, soon after I’ve told a client things are working, they’ll let me know they aren’t. Then I connect to the right database.
In a vacuum, or even as you sit reading this today, it’s easy to criticize someone else for making the mistake of connecting to the wrong database, or even forgetting to delete the object in master after I’ve created it in the right one. However, all of us are human. We’ll make mistakes. We’ll be pressed for time or sick or tired or want to leave or some other situation where we aren’t using all of our faculties. Even if none of those are true, we might depend on muscle memory because we’ve done this 1,000 times (or may 12 times in the last hour) and then don’t notice we’ve connected to the wrong database.
What might be even worse if when we’re updating objects and potentially update in one place but not another. What if v7 of the proc is in the user database, but v5 is in master. Who knows if we (or the client) will notice things aren’t working as expected right away.
Mistakes are one of the reasons I try hard to get everyone to follow a DevOps approach. Put your code in version control (so you know where it is when you make mistakes), use automation and CLI calls to deploy code. Whether you use something like Flyway or your own SQLCMD scripts, make sure things are automated. Then ensure you have logs with commands run, and if needed, output from the scripts.
We’re human, we make mistakes, so figuring out how to automate a process and then ensuring that automation is running results in repeatable, reliable deployments. Something we should all want, especially the control-obsessive DBAs.
Steve Jones
Listen to the podcast at Libsyn, Spotify, or iTunes.
Voice of the DBA 
I disagree with not only Chaad’s stance about not using sp_ in the name but also with Joe Celko’s repsonse and yes I know he’s the guy that wrote SQL FOR SMARTIES a book every SQL person shouodl have.
While I understand Chad’s point about SQL Server checking teh master DB first if your SP starts with sp_ however that is just a small probably immeasurable step in terms of performance and as far as duplicating name goes, if you are using a naming convention that includes something to help make it unique (in our case we use WAM an abbreviation of my company’s name). In the RARE event that somehow still ends up matching the same name as an SP in the master DB it’s almost impossible that it will have teh same params and or return the same data so if you are testing your SP you should during t3esting find out if it just happens to match the name of an SPO in master because it won’t work as expected.
Addressing Celko’s points, I believe appending columns with letters like i for Integer or s for Text is not at all a problem. Let’s say we do this on our DB and I get killed in a car wreck so they have to bring a new guy in. Without having to learn/memorize the DB’s structure he’ll know that any column that begins with an I is going to be an integer. Take that one step further, if he wants to find a column in a table that stores email as text he knows that the name is going to almost certainly match %email%. If he wants to find any cases where email is a FK to another table that stores the email as text he’ll know it will most likely match the name %iEmail%. The pint of using that kind of naming convention has nothing to do with making sure teh DB knows what’s what but so the person responsible for it’s maintenance knows what things will most likely be named.
LikeLiked by 1 person
I think it’s less you’ll have the same name for the proc and more that you’ll create an old version in master as a mistake and reference that. I’ve seen far too many objects in master because people connect to the server, wrong db, create a proc, then realize, then change dbs and create there, but forget to remove the one in master, which ages and is old.
For parameter names, I don’t know it matters, but I don’t like the prefix because I’ll see someone do this:
create proc myproc @cstartdate varchar(10) …
and then later we realize that’s bad, so we change to
create proc myproc @dstartdate varchar(10) …
For any .NET code, we have to refactor that since we should be using named parameters to prevent injection. Not a lot of work, but it is work, and across multiple apps, this can be hard to coordinate.
I think it’s really unnecessary for parameters. We ought to be able to view code in a VCS, so we can find the types. I do like naming conventions, but as simple as possible.
LikeLiked by 1 person
I don’t use VS for any SQL stuff only SSMS so there’s that. I have it I just prefer SSMS probably b/c that’s what I started off with 20+ years ago with SQL Server 7 ( I think it was version 7).
For something like
create proc myproc @cstartdate varchar(10)
I would do
create proc sp_myproc_WAM @sStartdate varchar(10)
I use s b/c our primary accounting DB which we didn’t design/create but was done by the software vendor, uses s for any type of text and so I stick with that.
s = Text
dt = Any Date/Time values (not dates stored as text)
i = Integer
h = PK/FK
That kind of convention. I don’t know how I would have done it if we had created it all from the beginning.
LikeLike
“I think it’s less you’ll have the same name for the proc and more that you’ll create an old version in master as a mistake and reference that.”
Exactly. I agree that it would rarely be a problem, but I’ve seen the stars align just right (or wrong) too many times. It’s an easy rule to follow to avoid the risk.
LikeLike
Steve – Want a fun read? Check out the below which NATURALLY was in a developers newsletter. Kills me how procedural and object based devs will go to great lengths to simply avoid learning how to think using set based logic
https://www.infoworld.com/article/3711272/9-reasons-sql-has-got-to-go.html
LikeLike
lol, I ran across that a day or two ago. It’s wild.
LikeLiked by 1 person