FETCH NEXT FROM curs1 INTO @lngProject, @Currency, @dblAnnualSave, @dblAnnualSpend, @dblProjAnnualSave

UPDATE #DollarValues
SET AnnualSave=@dblAnnualSave, AnnualSpend=@dblAnnualSpend, ProjAnnualSave=@dblProjAnnualSave
WHERE P_ID = @lngProject

--If the Currency is Dollars append all Values to Temp Table
IF @Currency ='USD' 
--Append Values to Temp Table
UPDATE #DollarValues
SET AnnualSave = @dblAnnualSave, AnnualSpend=@dblAnnualSpend, ProjAnnualSave=@dblProjAnnualSave
WHERE P_ID = @lngProject

UPDATE d
  SET 
  AnnualSave = p.ExpectedAnnualSaving, 
  AnnualSpend = p.AnnualSpend, 
  ProjAnnualSave = p.ProjectedAnnualSaving
FROM #DollarValues d INNER JOIN tblProjects p
ON d.P_ID = p.P_ID
AND p.ProjCurrency = 'USD'

quote:

---

Originally posted by barlowr70

I need to produce a report that involves currency conversion. All Report costs need to be reported in Dollars. What I've done is create a temporary table to hold the values, which are Spend, Save and ProjSave. I then set up a cursor to pick up the actual values. I loop through the records. If the Currency is dollars I update all values, otherwise I set up another loop and pass the currency values and the loop counter to another procedure that returns the converted values, then depending on the counter value I update a single field in the Temp table, but I'm having a problem with the second loop, it's falling over and terminating the outer loop also. I've included the code below.

Many Thanks.

CREATE PROCEDURE dbo.spConversionTest
AS
BEGIN
SET NOCOUNT ON

--Step one: create a temp table to hold the results of your query
create table #DollarValues (P_ID int not null, AnnualSave float, AnnualSpend float, ProjAnnualSave float)

--step two: Populate temp table with the ID of each Project.
INSERT #DollarValues (P_ID)
SELECT p.ProjID
FROM tblProjects AS p

Declare @lngProject int
Declare @Currency varchar(5)
Declare @dblAnnualSave float
Declare @dblAnnualSpend float
Declare @dblProjAnnualSave float
Declare @dblUSD float
Declare @dblGBP float
Declare @varDate datetime
Declare @myValue float
Declare @intCounter int

Declare curs1 Cursor
FOR
SELECT p.ProjID, p.ProjCurrency, p.ExpectedAnnualSaving, p.AnnualSpend,
p.ProjectedAnnualSaving
FROM tblProjects AS p

OPEN curs1
FETCH NEXT FROM curs1 INTO @lngProject, @Currency, @dblAnnualSave, @dblAnnualSpend, @dblProjAnnualSave
--Start Loop
WHILE @@fetch_status = 0
BEGIN
--Move to Next Record
FETCH NEXT FROM curs1 INTO @lngProject, @Currency, @dblAnnualSave, @dblAnnualSpend, @dblProjAnnualSave

UPDATE #DollarValues
SET AnnualSave=@dblAnnualSave, AnnualSpend=@dblAnnualSpend, ProjAnnualSave=@dblProjAnnualSave
WHERE P_ID = @lngProject

--If the Currency is Dollars append all Values to Temp Table
IF @Currency ='USD'
--Append Values to Temp Table
UPDATE #DollarValues
SET AnnualSave = @dblAnnualSave, AnnualSpend=@dblAnnualSpend, ProjAnnualSave=@dblProjAnnualSave
WHERE P_ID = @lngProject

--If the Currency is not Dollars
--**** PROBLEM AREA ****
ELSE IF @Currency !='USD'
SET @intCounter=0
--Start Loop, I'm having a problem with this loop and it sets the @@fetch_status to 0
--I need to take the cursor variables and pass them to the dbo.spConversionValue procedure

WHILE @intCounter < 3 BEGIN
DECLARE @dblConversion float
EXEC dbo.spConversionValue @dblConversion OUTPUT, @intCounter, @Currency, @dblAnnualSave, @dblAnnualSpend, @dblProjAnnualSave

--Depending on the Counter value updaet a field in the Temp Table
if @intCounter = 1 --Annual Saving
UPDATE #DollarValues SET AnnualSave = @dblConversion WHERE P_ID = @lngProject
else if @intCounter = 2 --Annual Spend
UPDATE #DollarValues SET AnnualSpend = @dblConversion WHERE P_ID = @lngProject
else if @intCounter = 3 --Projected Saving
UPDATE #DollarValues SET ProjAnnualSave = @dblConversion WHERE P_ID = @lngProject
--Increment the Counter
Set @intCounter=@intCounter+1
END
--**** END PROBLEM AREA ****

END
END

CLOSE curs1
DEALLOCATE curs1

select * from #DollarValues

---