Stored procedure for everything

Question

I had a heated discussion with a colleague on the usage of stored procedures (SP) in a .NET application (on an SQL server 2005 database). [He has a Microsoft background and I Java - which may or may not be relevant].

I have to insert data captured in the UI. For this I would write a SP and use that in the .NET code? It's not required but what are the pros and cons of using a SP?

Another scenario:

I am maintaining a list of cities. User can add cities using the UI. As you expect user cannot enter a duplicate city. An error will be shown if duplicate entry happens. this can be implemented in a number of ways:

1. In my code run a select query to check if it already exists and then if not insert the city otherwise an error on UI.
2. Insert directly and due to unique index an SQLException will be caught. Introspect the SQLException to check what unique index is violated and show the respective error.
3. create one SP and in that handle the above logic, i.e. checking for duplicate and throw error or insert

Which one is the right way? (links to good resources are welcome).

Answer 1

If you are intending to write one sp that will do all the checking and insert if it is able to, then why not return the number of rows modified and if it is greater than zero, your method was succesful. I would avoid having an exception, the idea is that not being able to insert is not really exceptional, just undesireable. Your insert method caling the stored proc can read an output parameter and tell it's caller if it inserted anything.

Answer 2

Firstly, it is a good idea to use SPs, rather than adhoc SQL statements because: 1) security - only need to grant permission to execute the sproc, NOT on the underlying tables 2) maintainability - can patch the SP within SQL Server without having to rebuild/deploy .NET code in order to tweak a query 3) performance - execution plan caching/reuse when using sprocs improves performance (can be done also if using parameterised SQL direct within your .NET) 4) network traffic (ok, negligible but SPs save you passing the whole SQL statement across the network, particularly if large query)

Exceptions are expensive, so try to avoid exceptions being thrown when you can prevent it. I'd recommend writing a sproc that does an IF EXISTS check prior to trying to INSERT a record and only inserting if it doesn't exist. Just return out a return code or output parameter indicating what happened. e.g. 0=inserted OK, -1=already exists

Do it all within the one sproc call to save DB round trips (i.e. don't query the db first to check, and then send another statement in to do the INSERT). Using an EXISTS check is the most optimal way of checking.

Answer 3

As a general rule of thumb, if the application is the only user of a given database schema, then I would advise using direct SQL (i.e. no SPs). If the database is shared between applications, it becomes much more critical to control the interface between applications and shared data, and the easiest way to define this interface is to control access to the data via stored procedures.

The use of SPs adds to the complexity of the system, and you shouldn't add complexity unless there's a good reason for it. It can also severely hamper your application's use of good ORM tools.

But as with all rules of thumb, there are always exceptions.

Answer 4

I like the idea of separating the data tier and the business logic tier. Therefore I am not putting any business logic to database but in code in my domain.

If you are using some kind of ORM like NHibernate, the role of the database will automatically become just a storage and nothing else. You need to be careful when you start implementing parts of the business logic on the database and another part in your application!

Best Regards

Answer 5

I don't think there is a one size solution. If you want common code between a Java and a.NET solution, then SP in SQL may be the best choice.

In the past I have avoided using any stored procedures as it make deployment in a controlled environment more difficult. Sure you can update a procedure here or there in a simple way but in a large system this can be more of a curse than a blessing.

It depends on how much control over your environment you would like. e.g. I prefer to have confidence that UAT and production are the same. i.e. if it works in UAT it will work in production.

If there is just the two of you, then it probably doesn't matter, you will be able to easily fix any inconsistencies between environments (assuming you have multiple environments)

However for a more complex system with more environments, multiple regions, developers in different time zones you will want the deployment and roll back of a new system as simple as possible. For a Java deployment, it just a matter of changing a symbolic link and restarting the service.

It is also fairly easy to have two versions of the software running at once so you can do last minute tests, change over and change back your production environment easily during the day rather than wait for down time. Perhaps there is a simple way to do this in SQL, but I haven't come across one.

In summary: agree on your requirements for the whole end-to-end process first and then find solutions to match, too often people start with solutions they are comfortable with, focus on one aspect to justify their choice. They often discover very late in the project a requirement which is a major headache which is too late to change, but it would have been simple if it had been considered from the start.