Stored Procedures - Do you know SQL stored procedure names should be prefixed with the owner?
2/01/2020 9:07 AM by
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:
- This prevents the database engine from checking for an object under the users schema first
- 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
Do you feel this rule needs an update?