SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

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

Shanew
Starting Member

USA
20 Posts

Posted - 02/05/2013 :  13:32:55  Show Profile  Visit Shanew's Homepage  Send Shanew a Yahoo! Message  Reply with Quote
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),

AS
BEGIN

-- 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"???
select.movenext
loop

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

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.
Thanks
Shane

James K
Flowing Fount of Yak Knowledge

3332 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:
SELECT
	SUM(Price) AS PriceTotals,
	SUM(Tax) AS TaxTotals,
	SUM(Tip) AS TipTotals
FROM
	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 TipTotals
FROM
	YourTable
This assumes that the "YourTotalsTable" already exists.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New 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