Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Looping through variables in SQL 2005.

Posted on 2009-07-14
6
Medium Priority
?
230 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

604 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