Skip to content

SA0015 : Avoid 'sp_' prefix when naming stored procedures

Using the sp_ prefix for custom stored procedures can lead to performance issues and confusion with system stored procedures.

In SQL Server, stored procedures beginning with the sp_ prefix are reserved for system stored procedures. When a stored procedure with this prefix is called, SQL Server first searches for it in the master database before checking the user database and schema. This can result in unnecessary performance overhead and can cause confusion if a custom procedure inadvertently shares a name with a system procedure.

Problematic stored procedure declaration:

CREATE PROCEDURE sp_CustomProcedure
AS
BEGIN
SELECT * FROM MyTable;
END;

The above example illustrates the use of the sp_ prefix. SQL Server will first look for sp_CustomProcedure in the master database, possibly causing a delay if the procedure is intended to reside in a user database.

  • Increased execution time when SQL Server searches the master database first.

  • Risk of naming conflicts with existing or future system stored procedures.

Avoid using the sp_ prefix when naming stored procedures to prevent performance issues and naming conflicts with system stored procedures.

Follow these steps to address the issue:

1.Identify stored procedures in your database that use the sp_ prefix. You can query the system tables to list all such procedures:

SELECT name
FROM sys.procedures
WHERE name LIKE 'sp_%';

2.Rename each stored procedure to remove the sp_ prefix. Use a distinct and meaningful naming convention. You can use the sp_rename system stored procedure for renaming:

3.Execute the renaming operation for each problematic procedure:

EXEC sp_rename 'sp_CustomProcedure', 'CustomProcedure';

4.Update any scripts, applications, or other dependencies that call the renamed stored procedures to use the new names.

For example, the corrected stored procedure declaration looks like this:

CREATE PROCEDURE CustomProcedure
AS
BEGIN
SELECT * FROM MyTable;
END;

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

Rule has no parameters.

The rule does not need Analysis Context or SQL Connection.

2 minutes per issue.

Naming Rules, Bugs

There is no additional info for this rule.

-- The 'sp_' stored procedure name prefix is reserved for system procedures and should be avoided
CREATE PROCEDURE sp_uspGetAllEmployees
AS
SET NOCOUNT ON;
SELECT LastName, FirstName, JobTitle, Department
FROM HumanResources.vEmployeeDepartment;
  Message Line Column
1 SA0015 : Avoid ‘sp_’ prefix when naming stored procedures. 2 17

Analysis Rules