When should I use a stored proc and when should I use a view?

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

 

 

 

About Kathi Kellenberger

I am a SQL Server professional working for Linchpin People. I love talking about SQL Server to anyone who will listen, just ask my two year old granddaughter. I love to write and teach. I am so humbled and honored to say that I made SQL Server MVP in 2013.
This entry was posted in T-SQL. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s