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
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.
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
[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?
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
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;
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
