mainrotor
asked on
I need help with using a temp table in a Stored Procedure in SQL SERVER 2008 R2
Hi Experts,
I have created a Stored Procedure in SQL Server 2008 R2. I created a few @-variables and named them @Principal, @Interest, @TotalPrincipal,@TotalInte rest. In my stored procedure I loop through a Temp Table I created. As I loop through the table I add @Principal to @TotalPrincipal, and I add @Interest to @TotalInterest. Once I am done with the loop I want my Stored Procedure to return the @TotalPrincipal and @TotalInterest values. How can I do this? Do I need another temp table? I have included a sample of my code. Thanks in advance for your help. -mrotor :-)
Here's a sample of my code:
Create table #DataTemp
(
PK int Identity(1,1)
Principal varchar(50),
Interest varchar(50),
)
INSERT INTO #DataTemp(Principal,Intere st)
SELECT Principal, Interest FROM RecordTrans
DECLARE @COUNTRAWDATA int;
SELECT @COUNTRAWDATA = COUNT(*) FROM #DataTemp
DECLARE @intCounter int;
SET @intCounter = 1
DECLARE @PRINCIPAL float;
DECLARE @TOTALPRINCIPAL float;
DECLARE @INTEREST float;
DECLARE @TOTALINTEREST float;
WHILE @intCounter <= @COUNTRAWDATA
BEGIN
SELECT @PRINCIPAL = Principal,
@INTEREST = Interest
FROM #DataTemp WHERE PK = @intCounter
@TOTALPRINCIPAL = @TOTALPRINCIPAL + @PRINCIPAL
@TOTALINTEREST = @TOTALINTEREST + @INTEREST
--INCREMENT MY COUNTER
SELECT @intCounter = @intCounter + 1
END
I have created a Stored Procedure in SQL Server 2008 R2. I created a few @-variables and named them @Principal, @Interest, @TotalPrincipal,@TotalInte
Here's a sample of my code:
Create table #DataTemp
(
PK int Identity(1,1)
Principal varchar(50),
Interest varchar(50),
)
INSERT INTO #DataTemp(Principal,Intere
SELECT Principal, Interest FROM RecordTrans
DECLARE @COUNTRAWDATA int;
SELECT @COUNTRAWDATA = COUNT(*) FROM #DataTemp
DECLARE @intCounter int;
SET @intCounter = 1
DECLARE @PRINCIPAL float;
DECLARE @TOTALPRINCIPAL float;
DECLARE @INTEREST float;
DECLARE @TOTALINTEREST float;
WHILE @intCounter <= @COUNTRAWDATA
BEGIN
SELECT @PRINCIPAL = Principal,
@INTEREST = Interest
FROM #DataTemp WHERE PK = @intCounter
@TOTALPRINCIPAL = @TOTALPRINCIPAL + @PRINCIPAL
@TOTALINTEREST = @TOTALINTEREST + @INTEREST
--INCREMENT MY COUNTER
SELECT @intCounter = @intCounter + 1
END
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
My bad. Misread the request.
Do you really need help!!!
What are the data types of Principal and Interest from RecordTrans???
Are they numeric/float or char/varchar???
Have you consider I/O and other Performances???
All that you need is :
SELECT @TOTALPRINCIPAL = SUM(Principal), @TOTALINTEREST = SUM(Interest) FROM RecordTrans GROUP BY 0;
GO
SELECT @TOTALPRINCIPAL as TotalPrincipal, @TOTALINTEREST as TotalInterest;
GO
Regards
NOTE: are Principal and Interest Text? if yes you need to convert them to numeric.
WRONG:
Create table #DataTemp
(
PK int Identity(1,1)
Principal varchar(50), <<<< WRONG DATA TYPE FOR NUMERIC OPERATIONS
Interest varchar(50), <<<< WRONG DATA TYPE FOR NUMERIC OPERATIONS
)
INSERT INTO #DataTemp(Principal,Intere st)
SELECT Principal, Interest FROM RecordTrans <<<< WRONG: USE SUM(..) FUNCTION
DECLARE @COUNTRAWDATA int; <<< YOU DON'T REALLY NEED THIS, SAVE MEMORY FOR IMPORTANT THINGS
SELECT @COUNTRAWDATA = COUNT(*) FROM #DataTemp
DECLARE @intCounter int;
SET @intCounter = 1
DECLARE @PRINCIPAL float;
DECLARE @TOTALPRINCIPAL float;
DECLARE @INTEREST float;
DECLARE @TOTALINTEREST float;
WHILE @intCounter <= @COUNTRAWDATA <<< YOU DON'T NEED THIS, SAVE PROCESSOR FOR IMPORTANT THINGS
FOR
BEGIN
SELECT @PRINCIPAL = Principal,
@INTEREST = Interest
FROM #DataTemp WHERE PK = @intCounter <<< YOU DON'T NEED THIS EITHER , SAVE I/O HDD AND MEMORY FOR IMPORTANT THINGS
@TOTALPRINCIPAL = @TOTALPRINCIPAL + @PRINCIPAL
@TOTALINTEREST = @TOTALINTEREST + @INTEREST
--INCREMENT MY COUNTER
SELECT @intCounter = @intCounter + 1
END
What are the data types of Principal and Interest from RecordTrans???
Are they numeric/float or char/varchar???
Have you consider I/O and other Performances???
All that you need is :
SELECT @TOTALPRINCIPAL = SUM(Principal), @TOTALINTEREST = SUM(Interest) FROM RecordTrans GROUP BY 0;
GO
SELECT @TOTALPRINCIPAL as TotalPrincipal, @TOTALINTEREST as TotalInterest;
GO
Regards
NOTE: are Principal and Interest Text? if yes you need to convert them to numeric.
WRONG:
Create table #DataTemp
(
PK int Identity(1,1)
Principal varchar(50), <<<< WRONG DATA TYPE FOR NUMERIC OPERATIONS
Interest varchar(50), <<<< WRONG DATA TYPE FOR NUMERIC OPERATIONS
)
INSERT INTO #DataTemp(Principal,Intere
SELECT Principal, Interest FROM RecordTrans <<<< WRONG: USE SUM(..) FUNCTION
DECLARE @COUNTRAWDATA int; <<< YOU DON'T REALLY NEED THIS, SAVE MEMORY FOR IMPORTANT THINGS
SELECT @COUNTRAWDATA = COUNT(*) FROM #DataTemp
DECLARE @intCounter int;
SET @intCounter = 1
DECLARE @PRINCIPAL float;
DECLARE @TOTALPRINCIPAL float;
DECLARE @INTEREST float;
DECLARE @TOTALINTEREST float;
WHILE @intCounter <= @COUNTRAWDATA <<< YOU DON'T NEED THIS, SAVE PROCESSOR FOR IMPORTANT THINGS
FOR
BEGIN
SELECT @PRINCIPAL = Principal,
@INTEREST = Interest
FROM #DataTemp WHERE PK = @intCounter <<< YOU DON'T NEED THIS EITHER , SAVE I/O HDD AND MEMORY FOR IMPORTANT THINGS
@TOTALPRINCIPAL = @TOTALPRINCIPAL + @PRINCIPAL
@TOTALINTEREST = @TOTALINTEREST + @INTEREST
--INCREMENT MY COUNTER
SELECT @intCounter = @intCounter + 1
END
Your original code is missing the "SET" keyword: SQL Server's T-SQL requires "SET" when setting a variable:
...
SET @TOTALPRINCIPAL = @TOTALPRINCIPAL + @PRINCIPAL
SET @TOTALINTEREST = @TOTALINTEREST + @INTEREST
...
But, even better, SQL allows you to do aggregating/summary functions -- such as SUM(), COUNT(), AVG() -- on an entire table or specified groupings of rows in the table.
For example, you can get the totals you want in this case using code like this:
DECLARE @COUNTRAWDATA int;
DECLARE @PRINCIPAL float;
DECLARE @TOTALPRINCIPAL float;
DECLARE @INTEREST float;
DECLARE @TOTALINTEREST float;
SELECT
@COUNTRAWDATA = COUNT(*),
@PRINCIPAL = SUM(Principal),
@INTEREST = SUM(Interest)
FROM #DataTemp
...
SET @TOTALPRINCIPAL = @TOTALPRINCIPAL + @PRINCIPAL
SET @TOTALINTEREST = @TOTALINTEREST + @INTEREST
...
But, even better, SQL allows you to do aggregating/summary functions -- such as SUM(), COUNT(), AVG() -- on an entire table or specified groupings of rows in the table.
For example, you can get the totals you want in this case using code like this:
DECLARE @COUNTRAWDATA int;
DECLARE @PRINCIPAL float;
DECLARE @TOTALPRINCIPAL float;
DECLARE @INTEREST float;
DECLARE @TOTALINTEREST float;
SELECT
@COUNTRAWDATA = COUNT(*),
@PRINCIPAL = SUM(Principal),
@INTEREST = SUM(Interest)
FROM #DataTemp
This is a typical procedural programming approach in the database context. You have to understand that in SQL language you don't need to loop through every record in a table like in a recordset in an application. In SQL language you should avoid the loops except when there is no other way, which is really rare.
Your code is an example of resource wasting and poor performance. In order to sum columns all you need is to use aggregate functions and grouping feature, if you group by some subsets. All in all you don't even need a stored procedure because you can do the same with 1 simple select statement:
Your code is an example of resource wasting and poor performance. In order to sum columns all you need is to use aggregate functions and grouping feature, if you group by some subsets. All in all you don't even need a stored procedure because you can do the same with 1 simple select statement:
SELECT SUM(Principal) as TotalPrincipal, SUM(Interest) as TotalInterest FROM RecordTrans
Maybe you also need some filter in a WHERE clause unless you really want to sum the entire table.
ASKER
ScottPletcher,
I changed my code to SET @TOTALPRINCIPAL = @TOTALPRINCIPAL + @PRINCIPAL
But when I do the SELECT at the end
SELECT @TOTALPRINCIPAL AS TotatPrincipal
GO
I just get a NULL.
Why is that?
I changed my code to SET @TOTALPRINCIPAL = @TOTALPRINCIPAL + @PRINCIPAL
But when I do the SELECT at the end
SELECT @TOTALPRINCIPAL AS TotatPrincipal
GO
I just get a NULL.
Why is that?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Open in new window