Needing help using variables in SQL LOOP 1

[JPMorgan]

GOAL: to write a script that will update a field in one table with the sum of information from two other fields in a different table. I want to loop through this so I don't have to write 60 separate statements.

EXAMPLE: UPDATE CF_DD_IN SET CF_DD_IN.10 = tblCASHFLOW.I1 + tblCASHFLOW.D1

PROBLEM: I declare and set my source and destination variables because their values need to increment with every loop, but when I use the variables in the UPDATE statement SQL is wanting to set the value of my destination variable equal to my source variable. I need a way around this. Any help/advise is much appreciated!

CODE:
--Update CF_DD_IN table with info from tblCASHFLOW table
declare @count int
declare @month int
declare @year int
declare @sourcefield varchar(50)
declare @destinationfield varchar(50)
set @count = 1
set @month = 10
set @year = 2002
while @count <= 60
begin
set @sourcefield = 'tblCASHFLOW.I[' + cast(@count as varchar) + '] + tblCASHFLOW.D[' + cast(@count as varchar) + ']'
--Should result in 'tblCASHFLOW.I1 + tblCASHFLOW.D1' on the first pass
set @destinationfield = 'CF_DD_IN.[' + cast(@month as varchar) + ']'
--Should result in 'CF_DD_IN.10' on the first pass

UPDATE CF_DD_IN SET @destinationfield = @sourcefield <--- Herein lies the problem
FROM tblCASHFLOW INNER JOIN tblSORT ON tblCASHFLOW.SORTno = tblSORT.[SORT No:]
INNER JOIN CF_DD_IN ON tblSORT.[FCSC Asset NO:] = CF_DD_IN.ASSET_NO WHERE cf_dd_in.cf_yr = cast(@year as varchar)

PRINT 'Month: ' + cast(@count as varchar) + ' = ' + cast(@month as varchar) + ' ' + cast(@year as varchar) +
' : SET ' + @destinationfield + ' = ' + @sourcefield
set @count = @count + 1
set @month = @month + 1
if @month = 13 begin
set @month = 1
set @year = @year + 1
end
end

 

[AWithers]

You need to do the whol thing as dynamic sql i.e build up the entire sql string in very loop and at the end of the loop execute it

e.g.

declar @sql varchar(500)
--build up the string using your variables

@sql = 'UPDATE CF_DD_IN SET '+ @destinationfield +'=' +'@sourcefield ' etc etc

--at the end of the loop execute the string

exec(@sql)


You may need to print out the ssql to check you have got the syntax right before you execute it

HTH

Andy

 

[rajeevnandanmishra]

JPMorgan,

If you still have problem try this:
*****************
declare @count int, @month int, @year int
DECLARE @strSql VARCHAR(255), @i varchar(2), @mainLoop smallint
SELECT @mainLoop = 1, @year = 2002, @count = 2, @month = 10
WHILE @count <= 60
BEGIN
SELECT @strSql = &quot;UPDATE CF_DD_IN SET I10 = tblCashFlow.I1 + tblCashFLow.D1 &quot;
SELECT @month = @month + 1
WHILE @month <=12
BEGIN
SELECT @i = CONVERT(VARCHAR(2),@count)
SELECT @strSql = #strSql + &quot;, SET I&quot; + CONVERT(VARCHAR,@month) + &quot; = tblCashFLow.I&quot; + @i + &quot; + tblCashFLow.D&quot; + @i
SELECT @count = @count + 1
SELECT @month = @month + 1
IF @count>60
BREAK
END
SELECT @strSql = @strSql + &quot;FROM tblCashFLow INNER JOIN tblSort ON tblCashFlow.SortNo = tblSort.[Sort No:] &quot; +
&quot;INNER JOIN CF_DD_IN ON tblSort.[FCSC Asset No:] = CF_DD_IN.ASSET_NO &quot; +
&quot;WHERE CF_DD_IN.Cf_Yr = &quot; + CONVERT(VARCHAR,@year)
PRINT @strSql
-- EXEC(@strSql)
SELECT @count = @count + 1, @month = 1, @year = @year + 1
END
********

I have made the EXEC statement comment. So that you should not execute the command in first time, Just check the printed sql first and if it seems ok then remove the Print statement and comment from the EXEC command.


Let us know if it helped.

Rajeev

 

[JPMorgan]

Andy thanks for your input! Works great! Until next time