Don't create stored procedures with prefix sp_

tsql

(Christian Rohr ) #1

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

(Colin Mueller) #2

Christian,

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

Colin


(Christian Rohr ) #3

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


(Adam Gabryś) #4

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


(Christian Rohr ) #5

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