using a view in the SQL script of a Stored Procedure

Question

using a view in the SQL script of a Stored Procedure

stevenplee

SSCommitted

Points: 1622
More actions
July 28, 2010 at 9:48 am

#235848

I am not an advanced TSQL scripting person. Everything I have learned about SQL has been self taught. However, I know how to write Select Statements, Create Store Procedures with Parameters, use views and multiple views, create multiple joins, etc. I am working on a project and created a SP that uses a view after the FROM clause. I was to told that this was not best practice for SQL.
My issue is that I haven't written TSQL which includes subqueries. What is the best/fastest way for me to learn how to improve my skills in writing TSQL using subqueries.
Thanks for any feedback!!!!!
Thanks

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

torpkev SSCarpal Tunnel Points: 4819 More actions · Answer 1

There are a multitude of ways to write t-sql well - classroom learning, book learning but mostly practice in my opinion.

What I don't particularly like is when people make sweeping statements like 'this way is better' without giving a good explanation (not you, whoever told you that) what I would recommend is looking up the execution plan of your sql statements, see how they perform - only then will you truly know which is the most efficient method of pulling the data.

--------------------------

I long for a job where my databases dont have any pesky users accessing them 🙂

SQLRNNR SSC Guru Points: 281324 More actions · Answer 2

Whoever told you that you can't use a view in a select statement has misled you.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

blackbird Ten Centuries Points: 1127 More actions · Answer 3

I agree with the sentiments above. You have been misled as you definitely can use views in the FROM clause of a SELECT statement. Also, you should definitely look at the execution plan of your query to determine if you should use a JOIN or SUBSELECT. Generally, JOIN performs better, but if you have a small amount of data, then it doesn't really matter.

As for the best way to learn how to improve your SQL Skills, that would be by writing more SQL. 🙂

WayneS SSC Guru Points: 95405 More actions · Answer 4

I think the problem with using a view in a select statement is if the view doesn't have all of the columns that you need, and you end up joining back to the same tables that the view is composed of. This would be an issue. Other than this, I completely agree with all others that there is no reason that you wouldn't use one. Using them in from clauses is the only way you can use one to begin with!

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

jvanderberg SSCertifiable Points: 6762 More actions · Answer 5

As everyone stated, there's nothing wrong with using a view in a FROM clause. However, when you JOIN a view against a table (or another view), you are typically doing so without the benefit of indexes. Still there's no hard-and-fast rule that says you shouldn't do that, but you will generally get better performance joining against a table with proper indexes (or an indexed view).

stevenplee SSCommitted Points: 1622 More actions · Answer 6

Thanks for all the input. The issue was that I created a stored procedure that was calling data from a view. I was told that it would eventually run slow as the data in the underlying table(s) increased.