c# - When is it better to write "ad hoc sql" vs stored procedures -

this question has answer here:

• what pros , cons keeping sql in stored procs versus code [closed] 47 answers

i have 100% ad hoc sql through out application. buddy of mine recommended convert stored procedures performance , security. brought question in mind, besides speed , security there other reason stick ad hoc sql queries?

sql server caches execution plans ad-hoc queries, (discounting time taken first call) 2 approaches identical in terms of speed.

in general, use of stored procedures means taking portion of code needed application (the t-sql queries) , putting in place not under source control (it can be, isn't) , can altered others without knowledge.

having queries in central place may thing, depending upon how many different applications need access data represent. find easier keep queries used application resident in application code itself.

in mid-1990's, conventional wisdom said stored procedures in sql server way go in performance-critical situations, , @ time were. reasons behind cw have not been valid long time, however.

update: also, in debates on viability of stored procedures, need prevent sql injection invoked in defense of procs. surely, no 1 in right mind thinks assembling ad hoc queries through string concatenation correct thing (although expose sql injection attack if you're concatenating user input). ad hoc queries should parameterized, not prevent monster-under-the-bed of sql injection attack, make life programmer easier (unless enjoy having figure out when use single quotes around values).

update 2: have done more research. based on this msdn white paper, appears answer depends on mean "ad-hoc" queries, exactly. example, simple query this:

select id, desc tblstuff item_count > 5 

... will have execution plan cached. moreover, because query not contain disqualifying elements (like other simple select 1 table), sql server "auto-parameterize" query , replace literal constant "5" parameter, , cache execution plan parameterized version. means if execute this ad-hoc query:

select id, desc tblstuff item_count > 23 

... able use cached execution plan.

unfortunately, list of disqualifying query elements auto-parameterization long (for example, forget using distinct, top, union, group by, or etc.), cannot count on performance.

if have "super complex" query won't auto-parameterized, like:

select id, desc tblstuff item_count > 5 or item_count < 23 

... still cached exact text of query, if application calls query same literal "hard-coded" values repeatedly, each query after first re-use cached execution plan (and fast stored proc).

if literal values change (based on user actions, example, filtering or sorting viewed data), queries not benefit caching (except when accidentally match recent query exactly).

the way benefit caching "ad-hoc" queries parameterize them. creating query on fly in c# this:

int itemcount = 5; string query = "delete tblstuff item_count > " +          itemcount.tostring(); 

is incorrect. correct way (using ado.net) this:

using (sqlconnection conn = new sqlconnection(connstr)) {     sqlcommand com = new sqlcommand(conn);     com.commandtype = commandtype.text;     com.commandtext =          "delete tblstuff item_count > @item_count";     int itemcount = 5;     com.parameters.addwithvalue("@item_count", itemcount);     com.prepare();     com.executenonquery(); } 

the query contains no literals , parameterized, subsequent queries using identical parameterized statement use cached plan (even if called different parameter values). note code here virtually same code use calling stored procedure anyway (the difference being commandtype , commandtext), comes down want text of query "live" (in application code or in stored procedure).

finally, if "ad-hoc" queries mean you're dynamically constructing queries different columns, tables, filtering parameters , whatnot, maybe these:

select id, desc tblstuff item_count > 5  select id, firstname, lastname tblpeeps      age >= 18 , lastname '%what the`  select id, firstname, lastname tblpeeps      age >= 18 , lastname '%what the`     order lastname desc 

... pretty can't stored procedures (without exec hack not spoken of in polite society), point mute.

update 3: here performance-related reason (that can think of, anyway) using stored procedure. if query long-running 1 process of compiling execution plan takes longer actual execution, , query called infrequently (like monthly report, example), putting in stored procedure might make sql server keep compiled plan in cache long enough still around next month. beats me if that's true or not, though.