Home
Stored Procedures - Do not start user stored procedures with system prefix "sp_" or "dt_"?
  v1.0 Posted at 13/11/2019 9:41 AM by Tiago Araujo

System stored procedures are created and stored in the master database and have the sp_ prefix. System stored procedures can be executed from any database without having to qualify the stored procedure name fully using the database name master. It is strongly recommended that you do not create any stored procedures using sp_ as a prefix. SQL Server always looks for a stored procedure beginning with sp_ in this order:

  1. The stored procedure in the master database.
  2. The stored procedure based on any qualifiers provided (database name or owner).
  3. The stored procedure using dbo as the owner, if one is not specified.

Therefore, although the user-created stored procedure prefixed with sp_ may exist in the current database, the master database is always checked first, even if the stored procedure is qualified with the database name.

Important: If any user-created stored procedure has the same name as a system stored procedure, the user-created stored procedure will never be executed.

Related rules

    Do you feel this rule needs an update?

    If you want to be notified when this rule is updated, please enter your email address:

    Comments: