Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
Shanew
Starting Member
20 Posts |
Posted - 2013-02-05 : 13:32:55
|
Hello,Im trying my hand at makeing SP's and im kinda new at it.. been doing code (VB and C# and simple TSQL for some time) but I want to do more with my SQL server.I a few different tables I need to run a select against, and then loop through the results adding some of the fields selected to themselves to get totales... And then take the totals for each field and insert it into a New table.. Below I try to cobble/pseudo code it the way I think it should be but knowing it is not the right syntext.CREATE PROCEDURE StoreTotales-- Setup a var tell the SP/Selct what table to use, name it TableToUse@TableToUse nvarchar(50),ASBEGIN-- Do a slect... get all Prices, taxs and Tip's from table definde in the @TableToUseSelect price, tax, tip from @TableToUse-- OK I dont know what to use here, in most code I would set up a recordset and call it RS, but I dont know in SPWhile select not EOFPriceTotals = PriceTotals + priceTaxTotals = TaxTotals + taxTipTotals = TipTotals + tip-- Again I know this is not right, how would i do a "MoveNext"??? select.movenextloop-- Now I need to insetr the values made in the loop into a new table..Insert into NewDBName PriceTotals PriceTotals AS [Price Total], TaxTotals AS [Tax Totals], TipTotals AS [TipTotals]ENDGOThe above is intended to illustrate what I would like to do... I know the "While select not EOF" is not right and other parts of it... In VB I would do "RS not EOF" but I dont understand if i even need to setup a RS or how...Any help here is appreciated.ThanksShane |
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-02-05 : 14:28:43
|
Here are some thoughts to get you started:First, SQL Server is not really well-set up to receive a table name as a parameter and base the queries on that. You could do it using DYNAMIC queries, but unlike in C# where dynamic keyword evokes pleasant feelings of run time type determination and such, dynamic queries in SQL are something that people try to avoid for a number of reasons. So assuming that you want to stick with a real table name, calculating the totals would be as simple as this:SELECT SUM(Price) AS PriceTotals, SUM(Tax) AS TaxTotals, SUM(Tip) AS TipTotalsFROM YourTable; You can group by various categories if you like - for example, if you have different locations, you could group them by locations to see the totalprice, totaltax and totaltips in each location.Now that you have the sums, inserting that into a new table is really simple - just modify it as follows:INSERT INTO YourTotalsTable(PriceTotals, TaxTotals, TipTotals)SELECT SUM(Price) AS PriceTotals, SUM(Tax) AS TaxTotals, SUM(Tip) AS TipTotalsFROM YourTable This assumes that the "YourTotalsTable" already exists. |
|
|
|
|
|
|
|