I would greatly appreciate your input on looping through variables in SQL 2005.
Im using a stored procedure to generate financial statements for up to 60 months in the future. I have 60 columns in a temporary table (#TempTable2) for storing the dollar values (the columns are named T1 to T60).
I also have 60 variables used for sub-totaling purposed, names @Tot1_1 to @Tot60_1.
Im trying to loop through the variables to calculate the tax amount and insert the values into #TempTable2, using the following code:
Whilst the 1st SET command generates the correct variable (eg @Tot21_1 when say @counter = 21) the second SET does not recognise this as a variable but assumes it is a string, as shown by the SQL output below:
Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable "@Tot21_1".
UPDATE #TempTable2 SET T21 = @Tot21_1 * 0.3000 WHERE (FinStmntID = 'TAX')
If however I change the 2nd SET Command as follows:
SET @Str = CONVERT(VARCHAR(20), @Tot21_1) the program recognises @Tot21_1 as a valid variable and executes correctly.
My question is how can a rewrite the 1st SET Command to enable SQL to recognise this as a variable and not a string?
Many thanks in anticipation.
DECLARE @Str VARCHAR(max)
DECLARE @Str1 VARCHAR(max)
DECLARE @Tot1_1 money
DECLARE @Tot60_1 money
SET @Counter = @FinMonth+1
WHILE @Counter <=60
SET @Str = '@Tot' + CONVERT(VARCHAR(2), @Counter) + '_1' -- 1st SET Command
SET @Str = CONVERT(VARCHAR(20), @Str) -- 2nd SET Command
SET @Str1 = 'UPDATE #TempTable2 SET T' + CONVERT(VARCHAR(2), @Counter) + ' = ' + @Str + ' * ' + CONVERT(VARCHAR(6), @TaxRatePerct) + ' WHERE (FinStmntID = ''TAX'')'
SET @Counter = @Counter + 1