Inline SQL vs Stored Procedures

Question

I know this is an age old debate but I'm curious as to what others do.

I tend to use stored procedures for complex reports, user verification and other queries that aren't your typical SELECT, INSERT, UPDATE, and DELETE statements. It's my understanding that SPs are faster because they save the execution stack but is there a tangable difference for your basic statements? What other benefits can be reaped for using SPs for your everyday basic SELECT, INSERT, UPDATE, and DELETE statements as it is quite tedius to create SPS for each basic query.

DUPLICATE

• http://stackoverflow.com/questions/15142/what-are-the-pros-and-cons-to-keeping-sql-in-stored-procs-versus-code
• http://stackoverflow.com/questions/462978/when-should-you-use-stored-procedures

Answer 1

We do not exactly know the data you're working with. You can decide for yourself by looking at the Execution Plan.

Answer 2

one of the issues we have with Sps is ... if its a "product" .. and you put a lot of the core logic in them , then you are in a way giving your source away to the client where you deploy the application .. there are ways around for it ....

in our team , we personally pefere to write inline .. please dont scream SQL injection .. that can happen with SPs also .. get all the data into the busines logic layer .. and write the logic in the dll or class ...

Answer 3

Mostly because you can change or tune them without re-deploying your app. Having all you DB one code is a good thing in my book. My asp.net app makes simple calls to the DB, and all the DB code is stored in the database as opposed to being scattered all around my asp source.

As for performance, I think SP's have the edge, but I think that edge has been reduced with some of the newer versions of SQL server so its probably not a deal clincher anymore.

Answer 4

Execution plans are cached for both stored procs and for dynamic queries, as long as you use parameters and not string concatenation. There wopuld be almost no performance difference for simple CRUD queries.

Answer 5

The SQL uses to cache the querys, so subsequent executions doesn't get compiled again, I use to check'em with this

USE Master
GO
SELECT 
UseCounts, RefCounts, CacheObjtype, 
ObjType, DB_NAME(dbid) as DatabaseName, SQL
FROM syscacheobjects
WHERE 1=1
AND sql LIKE '%yourSqlSearch%'
--AND UseCounts = 1
ORDER BY dbid, UseCounts DESC, objtype
GO

If you have a select query

SELECT field_list FROM table WHERE id = 2
SELECT field_list FROM table WHERE id = 3

these are cached as different querys, you can optimize it using a stored procedure

Answer 6

The "precompiled" benefit was nullified several versions back. If you use parameterized queries They are pretty much equivalent.

I don't quite understand your issue about tedium. You're going to need to write query, at least once, either way. In one case it's stored in the database, in the other in your source code. Possibly more than once in your source code, depending on how well you do DRY.