Views vs. Stored Procedures

Wow - I got to do these more often! I recently hired a new programmer, and we were having a discussion regarding the best way to handle complex queries involving lots of joins. Obviously, running one of those complex queries ad-hoc (even if it is somewhat optimized) will put a strain against your server. In certain situations, it definitely makes sense to use a 'pre-compiled' version of this query - but the question is, what kind?

You could create a stored proc that, when executed, returned the appropriate result set, or you could create a view that would allow you to query the joined tables as if they were actually one big happy table. While different schools of thought have evolved around this subject, I generally use the following rules of thumb to determine when it is appropriate to use either a Stored Proc or a View:

Use a stored proc if:

• You need to obscure specific details of the query from the querier
  
• You need to perform advanced logic or calculations against the data before returning it (beyond basic aggregation).
  
• You will only be using the data in one or two specific places and will not need to further filter, sort, or modify it.
  
  

Use a view if:

• You need the ability to further modify or manipulate the data - by specifying sorting or aggregates.
  
• You will be using this in enough places that maintaining a stored proc will be cumbersome to customize and maintain for each place.
  
• You want to select the specific output columns to be passed back to your application
  
• You need to join the results of the data to another table or tables.