Is there any benefit to using a TSQL view vs the equivalent stored procedure?

You cant filter a stored proc without passing in parameters. In a view, you can query it just as you would a table, adding any where clauses as necessary.

Right, just came to say the same thing. Apparently using stored procedures in queries is disallowed. – EndangeringSpecies May 4 at 19:06.

SQL Views are used for lot other purposes. 1) de a few columns from the original tables and create a view allot permissions only to the view for certain set of users. I guess this is one major use of a view.2) Combine 2 or more tables, have a derived column 3) Indexed views where unlike normal views have a allocated memory in contrast to that of a normal view where the execution plan shows the usage of main table.So you could actually use the indexed views work efficiently without referencing the main table.

1 good point about Indexed views which are often overlooked probably because they are a PIA to set up – Conrad Frix May 4 at 19:08.

If it's a choice between "select * from vwMyView" and "exec MyProc()", there's not a lot of difference. Both will return a result set that you can use however you'd like. One thing to note is that if you use a view, it can be joined to other tables, which may or may not apply to your situation.

If you want/need to filter the result set, you would want to use a view, as it's simple to add a where clause, where, with a proc, you would need to pass in parameters. If you want/need to use an existing proc as part of your source, you would need to use a stored procedure, as a view cannot reference a stored procedure. The "subtle bugs" you mention can occur with either method, though.

If you have a view A reference view B, and the query that materializes view B changes (without changing the number of columns returned), anything that depends on view A may break. The same is true of the stored procs: if proc B changes the way its result set is created, proc A may no longer work correctly. In general, you need to be careful when you have views reference views and procs referencing procs.In addition to the aforementioned issue, you can introduce some pretty serious performance problems.

Although, now that I think about it, I didn't actually test changing stored procedure PB. I think what I did was have procedure PA reference view VB, then I slightly changed view VB and procedure PA worked correctly. FWIW – EndangeringSpecies May 4 at 20:19 I guess it depends on what you mean by "change".

A view is nothing but a query, so if View A is relying on a column in View B, and you remove that column in View B, View A will be broken. The same is true of procs. If Proc A is relying on a column from a result set returned by Proc B, and you change Proc B to no longer return than column, Proc A is now broken.

Think of the view columns or proc result set(s) as the public interface. As long as you don't change the public interface, you won't "break" consumers, but you may alter their behavior in an undesirable way. – Andy Wilson May 5 at 18:20 that was not what I meant.

At least in the case of views, if you don't update vwConsumer after changing vwProducer ever so slightly, vwConsumer will still execute as if vwProducer never changed. You can put "TOP 1 PERCENT" into vwProducer and vwConsumer will still be getting all the rows as before. – EndangeringSpecies May 5 at 21:53.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.