Is the usage of stored procedures a bad practice?

Question

Hi,

We have an application that is written in C# that is connected to a ms sql server. We use to make a stored procedure for every database call, but then we've noticed that using stored procedures gives us a very big disadvantage, we don't know what stored procedures we need to update if we change our database.

Now I was wondering if using stored procedures is a bad thing or a good thing?

Answer 1

I believe SP are good for calculations/data manipulation/report data sources in the DB.

When using it solely for data retrieveal/updates to table rows you will run into a whole world of hurt.

This is the approach followed by some data access layers, and data retrievel sps for an individual row can become a pain.

So no, i would not recomend this as the best way to go.

Answer 2

Storage procedures are generally a good thing:

• They are more performant than standard querys, specially for certain operations.
• They help decoupling your database design team from your business design team.
• They give you good protection against sql injection
• They allow you to easily define separate user permissions for separate operations

Answer 3

Stored procedures are useful for enforcing constraints at the database level. It's easier to validate a handful of stored procedures restricting access to the database than it is to validate every bit of client code. So this makes them good.

Other than that, I'm a skeptic. I like to have everything in one place, in a language I can unit test.

Answer 4

You can't say that it is a good or bad thing. They have advantages and disadvantages and, depending on the project, their weight may differ.

Some advantages:

• They are executed by the DBMS directly, so no need for intermediate data transfer to middle layer, in case of multiple queries involved (complex logic).
• Allows you to have single layer of modifying the data in db.

Some disadvantages:

• You have the logic split between the middle layer (C# in your case) and persistence layer (DB), which might determine problems from maintenance point of view.

Answer 5

There are 2 views on this, some say they are evil, others swear by them. I take a middle of the road view on this.

Pros
Maintainability, if you need to change your query slightly without actually impacting other code, you can do this without needing to roll out new assemblies Security, no SQL injection attacks, unless you break best practices and build dynamic queries in the proc

Cons
Without documentation and standards, things can quickly spiral out of control, and make the database maintenance a nightmare.

Suggestions
Use them for reporting, and for more advanced database operations, but try to steer clear for simple CRUD operations.
Keep your business logic out of the database, that should be in a separate layer IMHO.

Answer 6

Stored procedures are good database practice -- you don't have to give your application arbitrary privilege to perform operations, which is a bonus from security and data integrity perspectives.

But like all code, you do need to organize it in some coherent fashion. For example, provide a class for each related set of database operations, whose methods are calls to stored procedures. With an adapter layer in place like that, changes to the database in response to changes in the business logic will be both obvious and well contained.

Answer 7

That's why you need good documentation and a good DBA for writing such software.

IMHO stored procedures are not bad, they can be used for many useful things like triggers, or performing some complicated queries where you would have to write many queries on the client side instead. But of course there is nothing that is only good. Some drawbacks that I found: stored procedures can cause much more work on the server side (what can be sometimes moved to the client side) and sometimes they are hard to maintain.

But on the other hand they are very useful when some day you'll have to give access to the database to some programmers writing software in e.g. Java who won't be able to use all those db classes that you wrote in C#. In that case it is good to have some logic in the database so you can use it regardless the client or language that is used.

Answer 8

That's not a SP problem, that's an issue of your development process. If you have no information you need - just get it.

You can make a simple visual map that shows your table schema and dependant SPs. If your DB is too large for visual mapping, add common text file that consists of your SPs and names of tables it depends upon.

Anyway, the bigger your DB is, the worse will be your job when inlining details of your schema into your application code. When you use SP you guarantee that this functionality won't be doubled and that most changes will occur on DB side without application recompilation and redistribution.

Upd

I forgot to mention one other thing. Good DB tools provide easy way to find dependant tables for each SP. There's 'View Dependencies' item in SP context menu in Microsoft SQL Management Studio, for example.

Answer 9

I have worked on projects that used stored procedures a lot. Basically, the business layer was moved to the database, because the team leader was impressed by some oracle guru he met in his previous job.

Stored procedure code is harder to maintain than C# (in Visual Studio), since the tools are worse, debugging is harder etc.

At the same time, having clear interfaces to your data rules. Thinking about which queries will be done on the database can be a good thing.

Try to keep the database generation and migration (update) code in source control. Include stored procedures there if you really want them. Keep stored procedure logic as simple as possible (don't do any business logic, just consistency style stuff). Maybe even generate them from a more abstract representation (along with the C# code to call them).

Answer 10

Stored Procedures are really good for queries that are very common, that will not change frequently. If you have a SP for "getname" that always pulls the first and last name from a table, it will be a good one to keep in the long run. Also, if you have a very complex query that could take a lot of horsepower on the client end, a stored procedure would help.

Any query that could be dynamic should not be a SP. If it's something that changes frequently, or something you need fast access to, it's a bad idea to make an SP. Here is why. Let's say you build a nice SP that gets a certain type of data. You have 3 different projects that use it. But you need something a little different, so your choices are:

1. Change the stored procedure and risk breaking all dependent applications
2. Creating a new stored procedure that's very similar for your function.

All in all Stored procedures are great for some needs, but not for others. Assess how much your needs might change, or what the disadvantages are to using a standard query.

Answer 11

Your wan't to know if DB schema changes affect SP. This means the team that changes DB doesn't write the SPs. In this context moving away from SP to inline SQL or ORM will not help you. Instead of checking SP you will have to check your code. I suggest you buy good tools that shows you dependencies between your tables and SP.

Answer 12

Another big advantage of stored procedures is that you can make changes on the backend on the fly, without requiring an application redeployment (as long as the prototype doesn't change).

At the large company for which I work, a code deployment is a MAJOR exercise, requiring at least 30 days and multiple approvals. A change to the DB can be done almost immediately.

Finally, remember that stored procedures can also offer protection against bad programmers. Got a great DBA but a team of cheapest-bid contractors writing your code? The DBA can write stored procedures and then remove DML permissions from the tables, forcing the code to go through the stored procedure to make any changes. That way, you don't have to worry that some guy is going to put some SQL in the code that accidentally wipes out half the DB.