Calling a Stored Procedure

Many people tend to miss a performance enhancement related to executing or calling a stored procedure. For example, if you wanted to call the [Ten Most Expensive Products] stored procedure in the northwind database, you can simply do it as:

     [Ten Most Expensive Products] 

You can skip EXEC(UTE), but you will need it if you run one stored procedure within another.

However, this is not the most efficient way to call a stored procedure. When such a command is processed, SQL Server has a hierarchical method of finding a stored procedure and executing it. First of all, it will take the login ID of the user who wants to execute the procedure and see if a stored procedure of that name exists. If it doesn't, then SQL Server will look for the stored procedure under the DBO login. Hence, always fully qualify your stored procedure with the owner.