Home
Stored Procedures - Do you know SQL stored procedure names should be prefixed with the owner?
  v2.0 Posted at 26/11/2019 11:07 AM by Tiago Araujo

​Always specify the schema prefix when creating stored procedures. This way you know that it will always be dbo.procedure_name no matter who is logged in when it is created.

There are 2 other benefits to including the schema prefix on all object references:

  1. This prevents the database engine from checking for an object under the users schema first
  2. Also avoids the issue where multiple plans are cached for the exact same statement/batch just because they were executed by users with different default schemas​.

Aaron Bertrand agrees with this rule - My stored procedure "be​st practices" checklist.

CREATE PROCEDURE procCustomer_Update @CustomerID INT, ….. BEGIN​

Figure: Bad example​​

​​​CREATE PROCEDURE dbo.procCustomer_Update @CustomerID INT, ….. BEGIN

Figure: Good example​​​​

Related rules

    Do you feel this rule needs an update?

    If you want to be notified when this rule is updated, please enter your email address:

    Comments: