Looping through variables in SQL 2005.

Hi All

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:
   
     @Tot21_1
     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.

James


DECLARE @Str VARCHAR(max)
DECLARE @Str1 VARCHAR(max)
 
DECLARE @Tot1_1  money
...
DECLARE @Tot60_1  money
 
...
 
SET @Counter = @FinMonth+1
WHILE @Counter <=60
BEGIN
									
   SET @Str = '@Tot' + CONVERT(VARCHAR(2), @Counter) + '_1'    -- 1st SET Command
   PRINT @Str
   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'')'
 
    EXECUTE (@Str1)
    PRINT @Str1
   SET @Counter = @Counter + 1
END

Open in new window

PhatMan007Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

TechSingerCommented:
OK, I think I'm starting to recognize what you are trying to do.  What you need to do now that you have the variable in a string is to use Execute to assign the variable.  I'm not at work, so I can't test for sure if you can put Set into an Execute, but you should be able to try it.

Old line:

SET @Str = CONVERT(VARCHAR(20), @Str)

Suggested line:

Execute ('SET @Str = CONVERT(VARCHAR(20), ' + @Str + ')')

Try it out.
0
PhatMan007Author Commented:
Hi TechSinger

Regretfully SQL still does not recognise the string as a variable.  The proposed new code of  
Execute ('SET @Str = CONVERT(VARCHAR(20), ' + @Str + ')')
causes SQL to error which causes code line 18 to error too as shown by the output below:

Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable "@Tot2_1".
@Tot2_1
Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable "@Tot2_1".
UPDATE #TempTable2 SET T2 = @Tot2_1 * 0.3000 WHERE      (FinStmntID = 'TAX')

The problem still appears to be in line 14 of the code - whilst it can generate the correct name of the variable in a string, SQL does not recognise this string as a variable.

Many thanks

James
0
Anthony PerkinsCommented:
>>SQL does not recognise this string as a variable.<<
In order to do that you will need to use sp_executesql instead of execute

However, you may want ot reconsider your design of having 60 columns.  This is not a good idea.
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

TechSingerCommented:
It appears that witin the Execute command, it cannot see external Scalar variable, so they have to be declared within the Execute command.
I would recommend setting the @Str variable to and then running put the rest of the code in the loop in one full Execute command.  I have included an example, but you may to to manipulate it so that it performs as you would like it.

SET @Str1='DECLARE '+@Str+' money
UPDATE #TempTable2 SET T' +  CONVERT(VARCHAR(2), @Counter) + ' = '
 + @Str + ' * '+ CONVERT(VARCHAR(6), @TaxRatePerct)+'  WHERE	FinStmntID = ''TAX'' '
 
    EXECUTE (@Str1)

Open in new window

0
PhatMan007Author Commented:
Hi acperkins and TechSinger

I have tried your suggestions but am still struggling with making it work.  To simplify the problem, I have written up a couple of lines of code which can run by themselves.  If we can get this to work then, I'll be able to transfer this to the bigger model.

Taking acperkins approach, I can get Code 1 to work however the Variables in the Exec statement (lines 13,14,15) are hard coded in.   If I convert, in the best way that I can, these hardcoded variables into true variables (lines 31,32,33) , the code does not run.  How should I modify this Code 2 so that it will work with true variables.

Trying the TechSinger approach, the code (Code 3)  runs but it returns a NULL result instead of the expected answer of 5,000.  Any ideas on how to change the above would be appreciated.

WRT acperkins comment "reconsider your design of having 60 columns" - The output via SSRS is a financial statement with  65 columns x approx 150 rows of data.  There are  5 cols of descriptive data and 60 cols (months) of financial data (ie 5 years)  All of the cols contain data.  The purpose of this wide temp table is to calculate and insert various sub-totaling type calculations like Tax payable etc before outputting the data.  Alternativly, I could run the tax calcs on the raw data and then pivot this raw data into the 65 coll final output table, hovever from what I have played with before, this alternative appears more complex.  Having said this, any feedback to a better design would be appreciated.

Many thanks

James
-- CODE 1 - THIS WORKS WHEN THE EXEC PARAMETERS ARE HARD CODED IN (See line 14 & 15)
 
DECLARE @Tot1_60 MONEY  -- 60 Variables which range from @Tot1_1 to @Tot1_60
DECLARE @Counter INT
DECLARE @Str nvarchar(1000)
DECLARE @Str1 nvarchar(1000)
 
SET @Tot1_60 = 10000
Set @Counter = 60
 
SET @Str1='SELECT @Tot1_' + CONVERT(VARCHAR(3), @Counter) +' * 0.5'
 
EXEC sp_executesql @Str1,
	N'@Str MONEY,@Tot1_60 MONEY',
	@Counter,@Tot1_60
 
 
 
-- CODE 2 - THIS DOES NOT WORK WHEN EXEC PARAMETERS CHANGED TO TRUE VARIABLES (See line 32 & 33)
 
DECLARE @Tot1_100 MONEY
DECLARE @Counter INT
DECLARE @Str nvarchar(1000)
DECLARE @Str1 nvarchar(1000)
 
SET @Tot1_100 = 10000
Set @Counter = 100
 
SET @Str1='SELECT @Tot1_' + CONVERT(VARCHAR(3), @Counter) +' * 0.5'
 
EXEC sp_executesql @Str1,
	N'@Str MONEY,@Tot1_' + CONVERT(VARCHAR(3), @Counter) +'',
	@Counter,'@Tot1_' + CONVERT(VARCHAR(3), @Counter)
 
 
-- CODE 3 - ALTHOUGH THE CODE RUNS, IT RETURNS A NULL RESULT, INSTEAD OF THE 
--          EXPECTED ANSWER OF 5000
 
DECLARE @Tot1_60 MONEY  -- 60 Variables which range from @Tot1_1 to @Tot1_60
DECLARE @Counter INT
DECLARE @Str nvarchar(1000)
DECLARE @Str1 nvarchar(1000)
 
SET @Tot1_60 = 10000
Set @Counter = 60
 
SET @Str = '@Tot1_' + CONVERT(VARCHAR(3), @Counter)
 
SET @Str1='DECLARE '+@Str+' money  SELECT ' + @Str + ' * 0.5'
 
EXECUTE (@Str1)

Open in new window

0
PhatMan007Author Commented:
Hi Guys,  I have regretfully run out of time and taken the unsophisticated approach of hard coding all the variables in.  Not that sexy but it works.   Many thanks for all your feedback.  James
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.