[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

I need help with using a temp table in a Stored Procedure in SQL SERVER 2008 R2

Posted on 2013-06-06
9
Medium Priority
?
578 Views
Last Modified: 2013-06-18
Hi Experts,
I have created a Stored Procedure in SQL Server 2008 R2.  I created a few @-variables and named them @Principal, @Interest, @TotalPrincipal,@TotalInterest.  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,Interest)
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
0
Comment
Question by:mainrotor
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +3
9 Comments
 
LVL 20

Expert Comment

by:dsacker
ID: 39226084
At the end of your stored proc, simply:
SELECT  Principal,
        Interest
FROM    #DataTemp

Open in new window

0
 
LVL 66

Assisted Solution

by:Jim Horn
Jim Horn earned 668 total points
ID: 39226086
>Once I am done with the loop I want my Stored Procedure to return the @TotalPrincipal and @TotalInterest values.  How can I do this?

CREATE PROC yourproc(@whatever as whatever)

-- Your SP code goes here.

-- Return recordset
SELECT @TotalPrincipal as TotalPrincipal, @TotalInterest as TotalInterest
GO
0
 
LVL 20

Expert Comment

by:dsacker
ID: 39226089
My bad. Misread the request.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 4

Expert Comment

by:BAKADY
ID: 39226315
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,Interest)
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
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 39226826
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
0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 39226965
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:

SELECT  SUM(Principal) as TotalPrincipal, SUM(Interest) as TotalInterest FROM RecordTrans

Open in new window

Maybe you also need some filter in a WHERE clause unless you really want to sum the entire table.
0
 

Author Comment

by:mainrotor
ID: 39227953
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?
0
 
LVL 27

Assisted Solution

by:Zberteoc
Zberteoc earned 664 total points
ID: 39227971
Because the default value when you declare a variable is NULL and in SQL any operation with a null will have null as result. You have to initialize your variables with zero or even better do:

SET @TOTALPRINCIPAL = ISNULL(@TOTALPRINCIPAL,0) + ISNULL(@PRINCIPAL,0)

Open in new window


With the SUM function you don't have this problem because null values are ignored. However, I hope you understood the point of not using the loop and any variables and stored procedure for that matter. It completely unnecessary.
0
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 668 total points
ID: 39229423
You have to initialize the totals to 0 before you add to them.  Otherwise they start out as NULL, and anything added to NULL only returns NULL.


DECLARE @PRINCIPAL float;
DECLARE @TOTALPRINCIPAL float;
DECLARE @INTEREST float;
DECLARE @TOTALINTEREST float;

SET @TOTALPRINCIPAL = 0
SET @TOTALINTEREST = 0


WHILE @intCounter <= @COUNTRAWDATA
BEGIN

    SELECT @PRINCIPAL = ISNULL(Principal, 0),
        @INTEREST = ISNULL(Interest, 0)

    FROM #DataTemp WHERE PK = @intCounter
     
    SET @TOTALPRINCIPAL = @TOTALPRINCIPAL + @PRINCIPAL
    SET @TOTALINTEREST = @TOTALINTEREST + @INTEREST

      --INCREMENT MY COUNTER
      SELECT @intCounter = @intCounter + 1
END
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

649 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question