SR0016: Avoid using sp_ as a prefix for stored procedures

Cause

One or more of your stored procedures has sp_ as a prefix.

Rule Description

In SQL Server, the sp_ prefix designates system stored procedures. If you use that prefix for your stored procedures, the name of your procedure might conflict with the name of a system stored procedure that will be created in the future. If such a conflict occurs, your application might break if your application refers to the procedure without qualifying the reference by schema. In this situation, the name will bind to the system procedure instead of to your procedure.

How to Fix Violations

To resolve this issue, you must replace sp_ with a different prefix to designate user stored procedures, or you must use no prefix at all. You should consider using database refactoring to update the name of any procedures that cause this warning. By using database refactoring, you can update not only the name of each procedure but also any references to that procedure throughout your database project. For more information, see Rename All References to a Database Object.

When to Suppress Warnings

You might suppress this warning if you cannot change applications that call your stored procedure.

Example

In the first example, the procedure name will cause this warning to be issued. In the second example, the procedure uses a usp_ prefix instead of sp_ and avoids the warning.

CREATE PROCEDURE [dbo].[sp_procWithWarning]
(
@Value1 INT,
) 
AS 
BEGIN
-- Additional statements here
RETURN 0;
END

CREATE PROCEDURE [dbo].[usp_procFixed]
(
@Value1 INT,
) 
AS 
BEGIN
-- Additional statements here
RETURN 0;
END

See Also

Concepts

Analyzing Database Code to Improve Code Quality