Just a thought...

I have seen many stored procs that contain SQL of the type

'...SELECT....From Table A Join Table B on B.X = A.Z...'

Would it not be better to put the select sql into a view and have the
stored proc reference the view?

advantages are

1/ the view can be re-used.
2/ the stored proc code is cleaner.

Are there any disadvantages?

Any thoughts would be appreciated.

(these stored procs were not written by me ).

code reuse and clean code are both good ideas

disadvantage? seems like extra work for nothing

ask yourself: will there be any other stored procs with exactly the same query requirement?

didn't think so

You haven't seen some of the monsters I have to deal with.

Instead of using views the 'designer' has opted to use a stored proc containing a select query in many cases. Also they don't use nested procedures.

What I think I may have been trying to say was something along the lines of complex problems can be solved by better design.

Complex solutions can be built from small simple building blocks.

Regards

Shaun McGuile

Also what about protection....

Table structure changes (field name change) - only dependancy is the view

The view renames/aliases all of its columns.

The stored procedure is protected by using the view...

Regards
Shaun McGuile

Yes, code re-use is a good thing up to a point. But putting logic into views can actually make it more difficult to debug the objects that call them. I had to modify a system once that had no less than six levels of views callling views, and it was an absolutle nightmare.
As a practical matter, I do not use views for this purpose. On occasion I have created user-defined functions for commonly used code.

I like views for producing a virtual table that represents some application of business logic on the database. You do, however, need to be careful with them. Since this is not a vendor specific forum I can't speak for all rdbms' (not that I can anyway) but you certainly don't want the nested view problem blindman mentions nor the inefficiency of the one-huge-flattened-database-view that you use for everything irrespective of whether or not you use all 180 joined tables (an exaggeration but I'm sure you get the point).

pootle flump ur codings are working excelent.