Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 SP with Loop and RS.MoveNext???
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Starting Member

20 Posts

Posted - 02/05/2013 :  13:32:55  Show Profile  Visit Shanew's Homepage  Send Shanew a Yahoo! Message  Reply with Quote

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.


-- Setup a var tell the SP/Selct what table to use, name it TableToUse
@TableToUse nvarchar(50),


-- Do a slect... get all Prices, taxs and Tip's from table definde in the @TableToUse
Select 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 SP
While select not EOF

PriceTotals = PriceTotals + price

TaxTotals = TaxTotals + tax

TipTotals = TipTotals + tip

-- Again I know this is not right, how would i do a "MoveNext"???

-- 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]

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

James K
Flowing Fount of Yak Knowledge

3873 Posts

Posted - 02/05/2013 :  14:28:43  Show Profile  Reply with Quote
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:
	SUM(Price) AS PriceTotals,
	SUM(Tax) AS TaxTotals,
	SUM(Tip) AS TipTotals
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)
	SUM(Price) AS PriceTotals,
	SUM(Tax) AS TaxTotals,
	SUM(Tip) AS TipTotals
This assumes that the "YourTotalsTable" already exists.
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000