Post reply

Modular Set Based Design

  • December 22, 2008 at 1:41 pm

    #203273

    Shortly, I'm going to be able to shape the creation of a brand new database using SS2K8. One thing that I'm getting a bit tired of doing is using the same segment of code in dozens of stored procedures only to find out down the road that it needs a slight modification made to it. At that point, I need to track down everywhere I've used that block of code to fix it. This is a nightmare even for rarely used pieces, and gets worse the more you use something.

    For example, our AR table stores invoices, payments, credits etc. all in the same format with a different 'Type' designator to tell you whether to add or subtract them. To pull the information from this table, I have a script involving multiple self joins. Many queries involve AR information in one way or another, and I find myself using this piece of code quite commonly.

    What I want to be able to do, is take this piece of code and save it to where I can just re-use it in many different SP's. That said, I want it to pass tables of data back and forth, not single lines. (I'm looking to maintain my set based structure, just make it re-usable and centrally located) I'm sure I can cover a lot of the bases using table valued functions and views, but I was wondering if anyone has any feedback / tips / strategies for making these little code modules.

    Is there anything new in 2K8 that makes this more feasible than it was in the past? Am I nuts for thinking about trying to move over to this structure? I can think of tons of advantages to this if I can get it set up properly, and can think of almost no negative apsects.

    Seth Phelabaum

    Consistency is only a virtue if you're not a screwup. 😉

    Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

  • December 22, 2008 at 3:26 pm

    #916445

    I am not aware of anything that would be nearly as good as Views. TVF's if you have to (preferably inline), but Views wherever you can.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

Viewing 2 posts - 1 through 1 (of 1 total)

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