Implementing set of processes in a stored procedure or through the code?

Question

I want to know what's the suitable method to implement the following case (best practice).

If i make a set of processes like this:

1. select data from set of DB tables.

2. loop on the selected result.

3. Make some checks on each iteration.

4. Insert the result in another table.

---

Implementing the previous steps in a stored procedure or in a transaction through my code (asp.net)? Concerning the performance, security and reliability issues.

Answer 1

Both will Work, which to choose depends on other factors not specified in your question.

Those factors include (but are not limited to)

1. Performance requirements, and other related factors (How much data needs to be shuffled back and forth between the server and DB)
2. Requirements to support multiple Backbends and or change backends
3. Talent available to maintain the code base

Answer 2

Any time you have to move data from the server to a client and back, you are going to hit a performance wall. It may be acceptable, it may not be. You will have to do the testing to figure that out.

I personally stick with stored procedures for my database access, because I like keeping the logic surrounding data with the data, rather than in the application layer, which consumes data.

The most concerning thing to me about your question is that you want to loop over rows--typically, that means you aren't using SQL correctly. SQL works best on sets, and database engines are optimized for set-based work. Unless you absolutely cannot work on the data in a set-based approach, I would advise you to avoid loops.