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.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 SP with Loop and RS.MoveNext???

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),

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

- Advertisement -