Solved

Looping through variables in SQL 2005.

Posted on 2009-07-14
6
223 Views
Last Modified: 2012-05-07
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

0
Comment
Question by:PhatMan007
  • 3
  • 2
6 Comments
 
LVL 3

Expert Comment

by:TechSinger
ID: 24856339
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
 

Author Comment

by:PhatMan007
ID: 24856399
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24859107
>>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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 3

Expert Comment

by:TechSinger
ID: 24860576
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
 

Author Comment

by:PhatMan007
ID: 24865857
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
 

Accepted Solution

by:
PhatMan007 earned 0 total points
ID: 24895095
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

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…

813 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now