SR0016: Avoid using sp_ as a prefix for stored procedures
RuleId |
SR0016 |
Category |
Microsoft.Naming |
Breaking Change |
Breaking |
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