Don't create stored procedures with prefix sp_

A good rule suggestion:

  • description of the Rule answering to the question “why?” : what’s the impact to keep this code as it is ?
    " * System stored procedure: a stored procedure that you create in the Master database and that is available to any other database. System stored procedure names start with sp_; the term “system stored procedures” is usually understood to mean the stored procedures Microsoft supplies with SQL Server. You can create your own system stored procedure simply by creating a stored procedure in Master and prefixing it with sp_. However, Microsoft recommends that you don’t use sp_ when you name a stored procedure. If you do, regardless of where you created it, SQL Server looks first in Master, then in the database you specified in your calling string, and finally (if you didn’t specify a database) in the local database with the Database Owner (DBO) as the owner. Yes, SQL Server checks Master first, even if you supply the database name. Not only is this inefficient if the procedure is local, but a procedure with the same name might exist in Master, leading to confusion" [https://www.itprotoday.com/microsoft-sql-server/all-about-stored-procedures, 24.11.2018]
  • snippet of Noncompliant Code
CREATE PROCEDURE dbo.sp_test
BEGIN 
(some t-sql statements)
END
  • snippet of Compilant Code (fixing the above noncompliant code)
CREATE PROCEDURE dbo.test
BEGIN 
(some t-sql statements)
END

Christian,

Please fill in all of the sections of the template for a good rule suggestion you copied and pasted into your post.

Colin

Unforunatly, I clicked a wrong button on my keyboard, now it’s completed :slight_smile:

2 Likes

snippet of Noncompliant Code and snippet of Compilant Code are the same :wink:

1 Like

Now it’s like it should be. Thank you :slight_smile:

1 Like

Sorry for the late reply.
There’s already a rule to enforce a naming convention for function and procedure names which forbids the “sp_” prefix by default. You can check the rule on SonarCloud.
I believe that it should do what you want.

1 Like

Thank you Pierre, yes it should be like this.

Cheers

Chris