I taught an Introduction to T-SQL class this week in Colorado. It was such a great week teaching and was probably the best group of students I have ever taught. There were lots of questions, and I realized that several would make potential blog posts.
One of the students asked how she would know when to create a view and when to create a stored procedure. One of the examples I gave concerned simplifying a pivot query. The base query could be really complex. To simplify things, it might make sense to create a view of the base query.
Since I have been doing this work for years, I instinctively know which structure to choose in each situation. We did not talk about user defined table valued functions in the class, so I won’t here, either. Also, the students will not be updating data, just writing queries for exporting or reporting.
Here are some of the things that help me make the decision between a view and a proc in the context of this class:
Create a stored procedure when…
- You need to pass parameters, for example, from SSRS
- There are multiple statements involved, such as populating a temp table with intermediary results.
- There is conditional logic involved with IF or looping with WHILE
Create a view when…
- You need to join the saved object to another table
- It’s just a query that you can reuse in other queries