The Myth of Stored Procedures Preference

Adel Khalil — Sat, 05 Jan 2008 22:16:28 GMT

When looking to the Stored Proscedures debate, there is always those three factors you should measure by.

Productivity

- Span code base over multiple staging environments is a pain and harder to maintain.

- Versioning sp code is way harder than versioning application code.

- Minor change to the design require changing in both the SPs and the DAL code.

- Todays IDEs are more advanced than most of the RDBMS offers, implementing on IDEs is obviously preferable.

- Switching between two seprate world to implement single method is always pain.

- It's impossible to cover every single scenario and write SP for it, which will lead to write these SP as you go, huge consistency problem.

- There is no way to only update single param in the Update method using SPs as there isn't optional parameters, on every update you need to supply full param collection.

- SPs are not portable if you want to develop application that run over multiple DBMS you will be writing SPs for each DBMS, standard SQL is portable.

Security

- Big myth over here, using SPs not guarante best security practice and certinly dosn't mean that your application is SQL Injection proof you can write code like this

string s = "EXEC sp_GetCustomerByEmail '" + txtEmailAddress.Text + "'";

and you will be using SP and still open to all kind of SQL Injection.

- Another myth regarding security is that if you are using Ad-hoc queries you *most likely* grand permissions for CRUD operations for your application user on the database, no you are not, that's why Views are invented.

Performance

often when SPs vs. Ad-hoc queries debate intoduced the performance card played, SPs advocates says SPs are pre-compiled which is not let met quate like Frans did from SQL Server Books Online

SQL Server 2000 and SQL Server version 7.0 incorporate a number of changes to statement processing that extend many of the performance benefits of stored procedures to all SQL statements. SQL Server 2000 and SQL Server 7.0 do not save a partially compiled plan for stored procedures when they are created. A stored procedure is compiled at execution time, like any other Transact-SQL statement. SQL Server 2000 and SQL Server 7.0 retain execution plans for all SQL statements in the procedure cache, not just stored procedure execution plans.

So with no pre-compilation and caching for both SPs and SQL statments there is no advantage for SPs here, in some other databases the SPs compiled into C or C++ but this isn't the case in SQL Server 7.0/2000.

I have introuduced my view on the SP vs. Dynamic SQL i don't see any benfit of SPs over the huge amount of productivity, performance that you will gain with dynamic SQL, the only benfit in peformance you will get it when using Managed SPs (SQL Server 2005) but for 0.7/2000 SPs isn't the right choice for most of the scenarios.

Search results matching tags 'Software Design', 'SQL Server', and 'Security'

The Myth of Stored Procedures Preference