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.