That's an excellent question. It all comes down to one basic issue: performance. Since stored procedures will execute faster than any dynamic SQL, if you have a very high throughput and volume application, you might want to consider it. However, in reality, the majority of applications are not that demanding so dynamic SQL would be just fine. That would make maintenance easier which could means you probably will ship faster. One approach I have seen work successfully is to develop the application with a wrapper layer on the database access. The wrapper is written completely with dynamic SQL. Once there's enough of the application put together to test, start performance testing. You'll quickly find that query or two that is taking up the bulk of the time. You can then change those into stored procedures. With the wrapper in place, the rest of the program probably won't even know about the change
Dig deeper on Windows Operating System Management
Related Q&A from John Robbins
What is a JAD session? This term is referred to often in job postings and seems like some kind of brainstorm you carry out with a client to define IT...continue reading
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.