Stored Procedures VS Views

[jaswinder_rana]

Hi:

can somebody please explain the difference?

I searched on internet but i couldn't quite get what they were trying to explain.

Thanks

[Dan Grossman]

A stored procedure is a program. It can be many, many lines long with lots of logic. It can return one or more result sets, or a single value, or nothing at all. With Microsoft SQL Server, you can even use the .NET CLR from within a stored procedure on the database.

A view is a single stored SELECT query. It is similar to a temporary table you can only read from, not insert or update, but you can use it from within other SELECT queries. Views simplify queries, provide stable interfaces to applications for read-only data, and make easier access control since they have their own permissions like real tables.

[jaswinder_rana]

Thanks.

Now, Let's say i have a Select query (maybe simple or complex. i understand INSERT etc. is not in scope of view). i can do the same query in stored procedure OR in view.

if you were me, which facts would make you choose SP or View?

[wwb_99]

You can INSERT or UPDATE views under some circumstances, but that is a side point, and a generally bad idea.

As for SELECTs, it depends on a few factors. If it is very complex, a SP might be better as they tend to be more performant. Same on if you want multiple result sets. Views are handy if you want to deal with the selected data like a table--like use it in JOINs with other tables or expose it directly to client apps like Excel or Access.

[jaswinder_rana]

AH thanks.

So, basically it sort of creates a temporary table from the select and i can use this view as a table of sorts, right??

[longneck]

exactly.

[jaswinder_rana]

wonderful. thanks


oh BTW, does using view , for a query, rather than procedure has any impact (positive or negative) on the resources (CPU, speed etc.)??

[longneck]

by simple virtue of it being a view? no. since a view is at it's heart a query, it could be written in multiple ways, each with different performance characteristics.