I've read a few posts on the stored procedure vs dynamic sql debate. I ran a few performance test for myself and it appears to be a wash.
Given that, I'm leaning toward dynamic sql mostly because it would mean one fewer place to have things.
But, before we go that route we wanted to ask the question:
Is there any compelling reason why we shouldn't abandon all of our stored procs and just write the SQL inside inside our functions in our business layer (essentially our data access layer)?
Or, is it just preference these days?
I was leaning toward procs, but I have to admit it would be nice not to have to keep up with all of them per all of our functions that call them.
Thanks,
Ron
That is sometimes a 'heated' discussion. How it goes usually depends upon whether the speakers background comes from the development world or the database world.
The Developer wants full and unfettered access to the data in order to easily create an application to solve a problem. The DBA wants to protect the data at all costs.|||Your tests may indicate a wash but SQL Server cannot cache a query plan for embedded SQL.
It isn't scalable.
Dynamic SQL has god-awful performance issues.
I cannot believe there is even a debate in this forum surrounding embedded SQL. i've read some of the other posts - and i can tell you that the reason DBAs do not like and frequently don't even allow dynamic/embedded SQL is because THEY KNOW MORE ABOUT SQL SERVER than developers. I couldn't write a connection string in C## to save my behind, but I can tell you that you should not under any circumstances use embedded SQL, and I know this from 10+ years of SQL Server experience. I've seen it used to hack. I've seen it bring a server to it's knees.
perhaps the developers who favor embedded or dynamic SQL have never monitored the SQL server during the use of such code. (?)
once you get actual data and multiple users hitting your database, in a real world environment, you WILL NOT see a 'wash' on performance.
The security issues are even worse. No organization that goes through even minimal security audits will allow embedded SQL in an application.
SQL Server may appear to be a fairly user friendly database, and it has a lot of stuff built in that makes it seem as if almost anyone can be a DBA. This is misleading. It is not just another MS Access "database".
If I were you, I would listen to actual DBAs on this issue.
