Solved

Looping through variables in SQL 2005.

Posted on 2009-07-14
6
227 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
Turn Insights Into Action

You’ve already invested in ITSM tools, chat applications, automation utilities, and more. Fortify these solutions with intelligent communications so you can drive business processes forward.

With xMatters, you'll never miss a beat.

 
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

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…

696 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