When to use Stored procedures vs embedding SQL statements?

Question

At my work I build software using C#, .NET, Flex, web services and stored procedures... We pretty much just call stored procedures for just about every interaction with our database, we do not embbed any sql statements at all.. So far it been working well, its just we use them for EVERYTHING! is that a bad thing? What woudl be a good time to use them versus just embedding the code. My boss says our whole reasoning for using them is to help stop "sql injection" in which when you think about it stored procedures seem pretty helpful with that sort, but the way we do things (call them using web services that are never exposed to the user) seems kinda pointless to use them.. considering there is not any real way for the user to enter sql statements thanks to the web serivce calls. What yah guyz think?

Answer 1

Embedding SQL statements may save you time and energy because you can quickly accomplish a task (i.e. you don't have to write a stored procedure).

However, in my experience, having SQL statements embedded in programs makes maintenance a pain. There isn't a way of making embedded SQL statements more manageable, maintainable, or readable than a call to a stored procedure.

Sometimes, it seems like a huge waste of effort to write 50 or 100 stored procedures to accomplish a small- or medium-sized task, and it would be so, so much easier to just embed SQL in your application. This is true -- it is a pain in the butt. However, you will thank yourself in a week, a month, or a year when you don't have to go debug a SQL statement and copy it to the 20 different locations in your program where that statement or a similar variant exists.

Regarding SQL injection: You'll never have a problem with it if you do not use dynamic SQL (whether dynamically building your SQL inside your application or inside the stored procedure). You could possibly argue that the web services layer provides some form of abstraction that lessens the chances of a SQL injection attack, but if you don't use dynamic SQL, you'll never have to worry about it.

Answer 2

Security issues aside, maintenance can become a nightmare when you hard-code SQL into any of your web services or applications.

Think about it this way:
Today, you have 5 web services in which you have hard-coded the same SQL query.

At 4:58PM on Friday, your boss calls you and says that they've moved a table that you query in all 5 web services and you have to change your query strings before you leave for the weekend because the 5 web services you maintain happen to be critical. So you work later than you wanted to because you have to modify code in 5 different web services.

If you were accessing a stored procedure, you would only need to change that one stored proc and be on your way home. Or, depending on where lines of responsiblity are drawn, the DBA's might handle it.

If you haven't already heard of it, you can find a lot of good information in the C# Database section of http://www.thecodeproject.com.

Answer 3

Look at it like this. What you are actually doing is mimicking object orientation. By writing procedures you are encapsulating your data access. These data access procedures are then held as part of the database. Easy to maintain and if the underlying objects and structures change, you can easily identify the procedures that have to be changed. This will make it unlikely that you have to amend the programs. It also allows for 'reusable code', i.e. you can use the same procedure over and over.