What are the pros and cons to keeping SQL in Store

Question

What are the advantages/disadvantages of keeping SQL in your C# source code or in Stored Procs? I've been discussing this with a friend on an open source project that we're working on (C# ASP.NET Forum). At the moment, most of the database access is done by building the SQL inline in C# and calling to the SQL Server DB. So I'm trying to establish which, for this particular project, would be best.

So far I have:

Advantages for in Code:

• Easier to maintain - don't need to run a SQL script to update queries
• Easier to port to another DB - no procs to port

Advantages for Stored Procs:

• Performance
• Security

Answer 1

I like stored procs, dont know how many times I was able to make a change to an application using a stored procedure which didn't produce any downtime to the application.

Big fan of Transact SQL, tuning large queries have proven to be very useful for me. Haven't wrote any inline SQL in about 6 years!

Answer 2

You list 2 pro-points for sprocs:

Performance - not really. In Sql 2000 or greater the query plan optimisations are pretty good, and cached. I'm sure that Oracle etc do similar things. I don't think there's a case for sprocs for performance any more.

Security? Why would sprocs be more secure? Unless you have a pretty unsecured database anyway all the access is going to be from your DBAs or via your application. Always parametrise all queries - never inline something from user input and you'll be fine.

That's best practice for performance anyway.

Linq is definitely the way I'd go on a new project right now. See this similar post.

Answer 3

Definitely easier to maintain if you put it in a stored procedure. If there's difficult logic involved that will potentially change in the future it is definitely a good idea to put it in the database when you have multiple clients connecting. For example I'm working on an application right now that has an end user web interface and an administrative desktop application, both of which share a database (obviously) and I'm trying to keep as much logic on the database as possible. This is a perfect example of the DRY principle.

Answer 4

Well obviously using stored procedures has several advantages over constructing SQL in code.

1. Your code implementation and SQL become independent of each other.
2. Code is easier to read.
3. Write once use many times.
4. Modify once
5. No need to give internal details to the programmer about the database. etc , etc.

Answer 5

I'm firmly on the side of stored procs assuming you don't cheat and use dynamic SQL in the stored proc. First, using stored procs allows the dba to set permissions at the stored proc level and not the table level. This is critical not only to combating SQL injection attacts but towards preventing insiders from directly accessing the database and changing things. This is a way to help prevent fraud. No database that contains personal information (SSNs, Credit card numbers, etc) or that in anyway creates financial transactions should ever be accessed except through strored procedures. If you use any other method you are leaving your database wide open for individuals in the company to create fake financial transactions or steal data that can be used for identity theft.

Stored procs are also far easier to maintain and performance tune than SQL sent from the app. They also allow the dba a way to see what the impact of a database structural change will have on the way the data is accessed. I've never met a good dba who would allow dynamic access to the database.

Answer 6

The performance advantage for stored procedures is often negligable.

More advantages for stored procedures:

• Prevent reverse engineering (if created With Encryption, of course)
• Better centralization of database access
• Ability to change data model transparently (without having to deploy new clients); especially handy if multiple programs access the same data model