Greetings All, currentley there is a heated discussion in my place of
work over which method is better/more efficient for simple selects.

Background:
1.) Simple Application that uses sql server for backened db.
2.) The application is only inserting and selecting data from the db.
3.) The developers want to use sp_executesql for simple selects and
the dba's want to use a stored proc.

>From my reading it seems that sp_executesql has a bit of overhead with
it and it is not as efficient as stored procs.

I would appreciate anyone's input on which would be better for simple
repetitive inserts to the db: Stored Proc, or sp_executesql?

Regards, TFD.

>>From my reading it seems that sp_executesql has a bit of overhead with
> it and it is not as efficient as stored procs.

If your developers are arguing for sql statements and your DBAs are for
stored procs, where did you get sp_executesql?

Typically sql statements are just sent by the application to the server,
without the use of sp_executesql. The situation where sp_executesql
would be used is if you had to dynamically generate a statement on the
server, you would build the statement in T-SQL, assign it to a variable,
and then execute it, so I don't think it's relevant here.

Getting back to your question, stored procs will almost always be
faster. SQL server caches the query plan and reuses it, making them
very efficient. There are other advantages too, like being able to
update the queries without recompiling your app, or touching the sql
generated by your developers. Since you obviously have people dedicated
to development and to database administration it seems you would get the
most benefit from stored procs. The developers can focus on code,
without having to worry about getting their sql right, and the DBAs can
focus on tuning those stored procs.

Greg Gilman