SA0015 : Avoid ‘sp_’ prefix when naming stored procedures

The topic describes the SA0015 analysis rule.

Message

Avoid ‘sp_’ prefix when naming stored procedures

Description

This rule checks for creation of stored procedure with names starting with “sp_”.The prefix sp_ is reserved for system stored procedure that ship with SQL Server.

Whenever SQL Server encounters a procedure name starting with sp_, it first tries to locate the procedure in the master database, then it looks for any qualifiers (database, owner) provided, then it tries dbo as the owner. So you can really save time in locating the stored procedure by avoiding the “sp_” prefix.

How to fix

Do not use the ‘sp_’ prefix when naming the stored procedure.

Scope

The rule has a Batch scope and is applied only on the SQL script.

Parameters

Rule has no parameters.

Remarks

The rule does not need Analysis Context or SQL Connection.

Effort To Fix
2 minutes per issue.
Categories

Naming Rules, Bugs

Additional Information

There is no additional info for this rule.

Example Test SQL
SQL
1-- The 'sp_' stored procedure name prefix is reserved for system procedures and should be avoided
2CREATE PROCEDURE sp_uspGetAllEmployees
3AS
4    SET NOCOUNT ON;
5    SELECT LastName, FirstName, JobTitle, Department
6    FROM HumanResources.vEmployeeDepartment;

Analysis Results
  Message Line Column
1 SA0015 : Avoid ‘sp_’ prefix when naming stored procedures. 2 17
See Also

Other Resources