Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2004
    Posts
    75

    Lightbulb Joins in stored procedures

    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 ).


  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2004
    Posts
    75

    Valid point but.....

    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

  4. #4
    Join Date
    Jun 2004
    Posts
    75
    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

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    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.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    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).
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •