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