Solved

Looping through variables in SQL 2005.

Posted on 2009-07-14
6
221 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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Suggested Solutions

I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

746 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

11 Experts available now in Live!

Get 1:1 Help Now