Stored Procedures - Do you know SQL stored procedure names should be prefixed with the owner?

Last updated by Brook Jeynes [SSW] 11 months ago.See history

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 "best practices" checklist.

CREATE PROCEDURE procCustomer_Update @CustomerID INT, ….. BEGIN

Figure: Bad example

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

Figure: Good example

We open source. Powered by GitHub