SQL Server - Stored Proc vs Module Code - Asked By chaz d'chaz on 23-Apr-15 09:47 AM

I guess this is probably a question that can be answered before it is finished being written: in general it is better to execute procedures wherever possible in a SP vs. module-level code with sql statements, no?

Yes.

Thx as always.  These things can be tough to work with.   I guess this is grown-up db stuff.

There are a couple of reasons for a yes answer on this.  Newer versions of sql server have drastically improved the performance of sql commands sent through ado.net on to sql server.  Which, used to be the main argument for procedures.  Now, there isn't a lot of difference on the "sql server side".

But, there is a difference in how ado.net handles requests deep inside the sql provider itself in .net.  The middle of this article talks about SqlClient.TdsParser.TdsExecuteSQLBatch vs. SqlClient.TdsParser.TdsExecuteRPC.  It is an old article but still a good read. 

https://www.nullskull.com/articles/adonet_exec_stored_procedures_sqlhelper.asp

To me, the biggest advantage using procedures is that you can force developers to access tables, view, etc.. only in the fashion approved in the stored procedure code.  This is big from a security perspective and big from a management perspective.  You can quickly look at all of the stored procedure source and see what is being executed and feel comfortable knowing what the risks are if changes are made to the database.  It also helps the dbas to manage what indexes might be needed to improve overall server performance.