2 ms·

I don't know about you, but I try to avoid hand-crafting SQL just like I avoid hand-crafting machine code/assembler. I use tools for that (either generators or

I don't know about you, but I try to avoid hand-crafting SQL just like I avoid hand-crafting machine code/assembler. I use tools for that (either generators or O/RM). Dynamic SQL doesn't have any specific advantage, correct, but neither do SProcs, so why put all your stuff in sprocs? Why not let a tool do the SQL and guide the tool to do the right thing? SP Security: If you don't trust your devs and applications against the DB, you've already lost. SP security is just mainframe-thinking and a lost cause. Efficiency: At least with SQL Server, there is no difference between SProcs and dynamic SQL (caveat: Parameterized queries, which should be the only type of dynamic SQL anyone is doing). They both get compiled and stuff in the parameter cache and are treated equal. This is as of SQL 7.0 (it's in the docs) Maintainability: Wha? This is the silliest argument I've heard. If you're swapping data around out from under the application you have HUGE maintainability DECREASES because now there is non-obvious, hidden magic going on. This is all data-centric thinking and DBA protectionism. The DB exists to serve applications which in turn serve users. Rather than pushing off applications, DB's should work as closely as possible with applications. "Consequently any dynamic SQL operation can be rendered more efficient by rewriting it as a SP." This statement makes no sense and has no bearing in fact.

giardini 19y ago
"I use tools for that (either generators or O/RM)." As long as you know what your tools are doing. "If you don't trust your devs and applications against the DB, you've already lost." No distrust is necessary. Consider developer error: mandating and restricting access to databases through SPs assures certain mistakes cannot happen (and makes clear where responsibility lies should they happen). This actually protects the developer. And many serious security attacks occur from inside an organization. Some corporations are criminally liable for violations of access: see the Sarbanes-Oxley Act. "SP security is just mainframe-thinking and a lost cause." "Mainframe-thinking" is increasingly relevant as data centers continue to centralize. You ignored what I said about efficiency. Indeed you ignored most of what I said. I sense you have an axe to grind but it's missing a head. Another developer error? Perhaps you can ask your DBA for a proper weapon?

giardini 19y ago

"I use tools for that (either generators or O/RM)." As long as you know what your tools are doing. "If you don't trust your devs and applications against the DB, you've already lost." No distrust is necessary. Consider developer error: mandating and restricting access to databases through SPs assures certain mistakes cannot happen (and makes clear where responsibility lies should they happen). This actually protects the developer. And many serious security attacks occur from inside an organization. Some corporations are criminally liable for violations of access: see the Sarbanes-Oxley Act. "SP security is just mainframe-thinking and a lost cause." "Mainframe-thinking" is increasingly relevant as data centers continue to centralize. You ignored what I said about efficiency. Indeed you ignored most of what I said. I sense you have an axe to grind but it's missing a head. Another developer error? Perhaps you can ask your DBA for a proper weapon?