SQL Server Performance

SQL 2008 - Stored Procedures

Discussion in 'Getting Started' started by Terry Wingfield, Aug 2, 2009.

  1. Terry Wingfield New Member

    Hi all, just after abit of advice, many replies would be great!
    I am currently studying for proffesional web developer (long way to go) ive created my DB to best of of my ability to date. it is well structured and links very nicely but what i am trying to figure out is how do i create a stored procedure that inserts a new record for each table, mmmmm like one large stored procedure that does it all.
    So ive got 5 tables and i would like to a create SP that inserts to all tables (if its possiable)
    Once ive figured that out, i could eventually do the same for SELECT, UPDATE and DELETE
    Many Thanks in advance
    Terry Wingfield
  2. RickNZ New Member

    Your SP may be able to just have 5 different INSERT statements. Same for UPDATE and DELETE.
    You can also have 5 SELECT statements, although that will produce 5 result sets, so your code needs to be aware of that.
    There are other ways to structure it, too, depending on the details of your schema and your application.
  3. Terry Wingfield New Member

    Thank you Rick for the quick reply. I've created them now, my last part is to create a calculation, one of my columns has price of products...and i have to test for cascades, any words of wisdom?
    Thanks Terry
  4. FrankKalis Moderator

    [quote user="Terry Wingfield"]
    Thank you Rick for the quick reply. I've created them now, my last part is to create a calculation, one of my columns has price of products...and i have to test for cascades, any words of wisdom?
    [/quote]
    Can you be a bit more specific and probably post your table structures, sample data, and required output?
  5. Terry Wingfield New Member

    Hi Frank, sorry for the lack of input....Soo to elaborate my table is PurchaseDetails. I have
    PurchaseDetailsId, DatePurchased,ItemLocation,PurchasePriceInGBP and LibraryItemId (some of these columns reference other tables)
    What im trying to achieve is to calcultate all of the fields in PurchasePriceInGBP maybe once executed creates a temporary record that shows the total figure. Is this possiable? my T-SQL is in progress.
    Thanks Terry
  6. FrankKalis Moderator

    If I understand you correctly, you're looking for some aggregation of the PurchasePriceInGBP column? If so, then have a look at the GROUP BY clause in the SQL Server Books Online. A statement could probably look something like this:
    SELECT DatePurchased, SUM(PurchasePriceInGBP) AS TotalPurchase
    FROM PurchaseDetails
    GROUP BY DatePurchased;
  7. Terry Wingfield New Member

    Hi Frank, absoloutly spot on, i tried messing around with it, also created a SP out of it, im starting to really enjoy this now, it took allot to get my head round it but its starting to sink in.
    Many Thanks
    Terry Wingfield
  8. FrankKalis Moderator

    Cheers and enjoy this discovery. [:)]

Share This Page