Click here to Skip to main content
65,938 articles
CodeProject is changing. Read more.
Articles / database / SQL-Server

Stored Procedure Naming Conventions

5.00/5 (1 vote)
17 Apr 2012CPOL1 min read 18.4K  
It is recommended that stored procedures shouldn't be prefixed with sp_ for two simple reasons.

Why Shouldn't We Begin a custom/user-defined Stored Procedure Name with sp_?

Ever wondered why we don't (or shouldn't, if you do) prefix procedures with an sp_?

As a primarily .NET developer, while doing any database work, earlier I hadn't paid much attention to why all stored procedures in the company where I worked in, began with a prefix of "p". So we would name them along the lines of "p_GetXyzDataByParam1Param2". Obviously, I could see that "p" is the convention in the company, but would it cause any impact if we name them the way system procedures are named (i.e., sp_? Turned out, that yes, it could.

We do know that the system stored procedures are named with _sp, and are stored in the master database. But if any stored proc does start with an sp_, SQL Server tries to find any such proc in the following order:

  • in the master database
  • based on qualifiers like database name or owner
  • using owner dbo if it's no owner is specified

In other words:

  1. The master database is always checked first, even if the proc is qualified with the db name.
  2. If any user defined stored proc has the same name as a system procedure, the system proc will be executed (and the user proc will never be executed).

Conclusion

It is recommended that stored procedures shouldn't be prefixed with sp_ for two simple reasons:

If a user-defined stored procedure has the same name as a system stored proc, the user-defined procedure will never be executed.

The master db will always be checked first because of the prefix of sp_.

  • Possible conflict of user created stored procedures with system stored procedures
  • For performance

References

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)