Stored procedures and network congestion When I went to an interview, the interviewer asked me "what are the benefits of stored procedures?" and I started answering but when I said "It reduces network congestion", he suddenly said "no, it doesn't".

I made a sudden pause, I was afraid to ask him why. After the interview session, I searched a lot on the Internet but I found the point I said was there but no explanation for it. So I asked myself and made some assertions:
Since a stored procedure is precompiled, i.e. an execution plan has already been created for it, it increases speed of execution.
But for cases where we pass some variables to the SP, those need to go to the server for execution, and should retrieve different results for each variable.

So my point is that the interviewer is correct: network congestion is not reduced, each time the variables have to be passed to the database and the results should be returned, so network is busy with these. Right?

Is my assertion correct?
In SQL Server, the query optimizer goes through these steps to execute any query (stored procedure call or ad-hoc SQL statement):


syntactically check the query  
if it's okay - it checks the plan cache to see if it already has an execution plan for that query  
if there is an execution plan - that plan is (re-)used and the query executed  
if there is no plan yet, an execution plan is determined   
that plan is stored into the plan cache for later reuse  
the query is executed  


The point is: ad-hoc SQL and stored procedures are treatly no differently. 

If an ad-hoc SQL query is properly using parameters - as it should anyway, to prevent SQL injection attacks - its performance characteristics are no different and most definitely no worse than executing a stored procedure.

Stored procedure have other benefits (no need to grant users direct table access, for instance), but in terms of performance, using properly parametrized ad-hoc SQL queries is just as efficient as using stored procedures.