Don't forget about Parameterized SQL

It's common to see .NET developers and SQL Server DBAs arguing over the merits of stored procedures versus inline (ad hoc) SQL. It's un­for­tu­nate that these folks are so polarised since there is a solution that meets somewhere in the middle. It's called pa­ra­me­terised SQL and it's similar to inline SQL, except that it's based on templates. You ef­fec­tive­ly have the SQL that exists in a stored procedure, and you specify input/output parameters in the same way as you do for stored procedures. This SQL is then placed in the data access layer of your ap­pli­ca­tion.

From what I understand, Microsoft are using this for DLinq and have dropped their rec­om­men­da­tion on the use of stored procedure. I'm all in favour of this method since it makes upgrading ap­pli­ca­tions so much simpler, and reduces your dependency on the DBA whilst main­tain­ing a level of protection from SQL injection attacks. There is the point about setting security on individual stored procedures - but how many people really do that? Even when they do they often leave themselves open to other attack vectors.

Tagged with performance, security and sql.