Bull Sharks and Stored Procedures: Two Things to Avoid

By November 29, 2010For Developers, Tips

Sharks Explore by Biting Because They Are Nearly Blind

Bull sharkMany consider bull sharks to be one of the world’s deadliest creatures. Despite their awesome prowess and speed, bull sharks are nearly blind in the deep, dark waters of the ocean. Rather than identifying friend, foe and food by inspection, they literally bump into and bite things they are interested in to determine what they are. What if they bite into something bad like barrel of oil or one of the few creatures they can’t defeat in a fight? There are consequences to this approach and the shark is really just hoping that the interesting object will behave well.

Stored Procedures Provide No Direct Metadata

Inspecting stored procedures is very similar. They have no true schema. They might return different things at different times, crash the database or delete all your data. Like sharks, having too many stored procedures around can really ruin your day. From a reporting perspective, they should be used as a last resort when every other conceivable option has been exhausted.

Let’s Not Party Like It’s 1999

Many developers believe that stored procedures will make their system faster and more secure. During the tech boom of the late 90’s, databases and web servers were being exposed to the world at large for the first time ever.  Before that, client-server desktop apps existed safely on internal networks where hackers, crackers, worms and competitors could not reach them.  When database applications started getting exposed to the world, vulnerabilities from injection attacks quickly became apparent. In response, stored procedures were used to limit exposure to certain types of attacks. Additionally, the query optimizers of the time could often cache predefined queries in stored procedures.

With the advent of SQL 2000, parameterized queries let you effectively prevent most injection attacks without the overhead and complexity of stored procedures. A decade after this innovation, many developers still resort to stored procedures out of habit. The lesson here is to use parameterized queries instead of stored procedures for security in nearly all cases.

Getting a Better View

Developers and DBAs also use stored procedures to simplify the database and shield complexity from reporting systems like Izenda Reports. The perfect tool for that is to use a database view.  Views are essentially queries that get exposed as virtual tables. Unlike a stored procedures, they are first class objects with their own schema, metadata and everything. They are also deterministic and there is little risk that they will do damage to your database or bog down the system. They solve all your problems with no compromises in most situations.  We are big fans of views after observing hundreds and hundreds of different databases.  Applications that avoid stored procedures are significantly faster, simpler and easier to maintain and scale.

Converting A Stored Procedure Into A View

create procedure [dbo].[GetProducts]
@PurchasedDate datetime, @SoldDate datetime AS
SELECT Products.ProductName, Products.UnitPrice
FROM Products
WHERE Products.PurchasedDate >= @PurchasedDate
ORDER BY Products.UnitPrice DESC

The stored procedure above would convert to the following view.  The parameters would be done through a reporting tool like Izenda in a much more flexible and user-centric manner.

create view [dbo].[ProductsView] AS
Products.ProductName , Products.UnitPrice, PurchaseDate
FROM Products
ORDER BY Products.UnitPrice DESC

This sample  shows how easy it is to convert a basic stored procedure into a view. Your may use temp tables or may be more procedural in nature and require a more involved transition.  It may be even more difficult to change the policies and culture of your organization. The effort will be worth it.