Stored Procedures - Do you know SQL stored procedure names should be prefixed with the owner?
Loading last updated info...
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: Figure: Bad example
CREATE PROCEDURE dbo.procCustomer_Update @CustomerID INT, ….. BEGIN
✅ Figure: Figure: Good example