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.


2 Answers 2


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
  • Don't you mean that the choice DOES depend on other factors not specified in the question? Oct 22, 2013 at 15:43

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.

  • Could u clarify what's the alternative to looping in DB plz,an example or some links ? Oct 23, 2013 at 7:16
  • 1
    Well, it all depends on what exactly you mean when you say "make some checks" at step 3. I am assuming that you are using SQL Server, which also affects the options you have. String concatenation can be relieved by FOR XMLPATH (sqlblog.com/blogs/aaron_bertrand/archive/2012/01/26/…), tasks which can be reduced to a recursive loop are easily handled by a recursive common table expression, etc. It depends very heavily on the case you are using it in.
    – mgw854
    Oct 23, 2013 at 15:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.