Export (0) Print
Expand All

SR0016: Avoid using sp_ as a prefix for stored procedures

RuleId

SR0016

Category

Microsoft.Naming

Breaking Change

Breaking

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

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.

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.

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

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

Community Additions

ADD
Show:
© 2014 Microsoft