Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2388
  • Last Modified:

TSQL variable reference

Hello,

i've got some code like so :

DECLARE @CURRENTPERIOD AS VARCHAR(20)
DECLARE @PERIOD_2 AS VARCHAR(20)
DECLARE @PERIOD_3 AS VARCHAR(20)
DECLARE @PERIOD_4 AS VARCHAR(20)
DECLARE @PERIOD_5 AS VARCHAR(20)
DECLARE @PERIOD_6 AS VARCHAR(20)
DECLARE @PERIOD_7 AS VARCHAR(20)
DECLARE @PERIOD_8 AS VARCHAR(20)
DECLARE @PERIOD_9 AS VARCHAR(20)
DECLARE @PERIOD_10 AS VARCHAR(20)
DECLARE @PERIOD_11 AS VARCHAR(20)
DECLARE @PERIOD_12 AS VARCHAR(20)
DECLARE @PERIOD_13 AS VARCHAR(20)

IF @STARTYEAR = @ENDYEAR
WHILE @COUNT <> 13
BEGIN      
/*  INCREMENT AND RE-INITIALISE PERIODEND AND COUNTER  */
SET @COUNT = @COUNT + 1
SET @PERIODCOUNT = @PERIODCOUNT + 1
SET @CURRENTPERIOD = 'FC' + @STARTYEAR + '_P' + CAST(@PERIODCOUNT AS VARCHAR(20))

/* UPDATE @PERIOD_ VAR FOR LATER USE */
SET @PERIOD_+[@PERIODCOUNT] = @CURRENTPERIOD
END      

In the last line of code, I'm trying to dynamically update variables @PERIOD_2 to @PERIOD13 with the
value in  @CURRENTPERIOD.  

Thanks
0
show_t
Asked:
show_t
  • 3
  • 2
1 Solution
 
mcmonapCommented:
Hi show_t,

You cannot do this in the way you want, you could do it with a series of IF statements:

/* UPDATE @PERIOD_ VAR FOR LATER USE */
IF @PERIODCOUNT = 1 SET @PERIOD_1 = @CURRENTPERIOD
IF @PERIODCOUNT = 2 SET @PERIOD_2 = @CURRENTPERIOD
IF @PERIODCOUNT = 3 SET @PERIOD_3 = @CURRENTPERIOD
IF @PERIODCOUNT = 4 SET @PERIOD_4 = @CURRENTPERIOD
--..etc..
0
 
show_tAuthor Commented:
Thanks mcmonap,

I realise that method already, but i'd STILL like
to know how to dynamically call up variables names
the way i originally asked.

Thank-you
0
 
mcmonapCommented:
Hi show_t,

Like I said "you cannot do this in the way you want" - at least I don't think so, perhaps someone else has a thought?

The closest you could do I think would be to hold the period variables in a table variable instead and update them there:

DECLARE @tblTemp TABLE (Var VARCHAR(15), Val INT)
INSERT INTO @tblTemp (PERIOD_1, 0)
INSERT INTO @tblTemp (PERIOD_2, 0)
INSERT INTO @tblTemp (PERIOD_3, 0)
INSERT INTO @tblTemp (PERIOD_4, 0)
--..etc

/* UPDATE @PERIOD_ VAR FOR LATER USE */
UPDATE @tblTemp SET Val = @CURRENTPERIOD WHERE Var = 'Period_' + CAST(@PERIODCOUNT AS VARCHAR(4))

Obviously you would have to change how the rest of the procedure accesses these variable values, ie selecting them from table rather than directly...
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
mcmonapCommented:
Sorry insert statements a mess - missing VALUES key word and text identifiers should have beenformatted as below:

INSERT INTO @tblTemp VALUES ('PERIOD_1', 0) --..etc
0
 
BillAn1Commented:
it's a bit messy, beacue you need to use dynamicSQL. In normal circumstances you coudl try something like
EXEC ('SET @PERIOD_'+@PERIODCOUNT + ' = ' + @CURRENTPERIOD)
unfortunately, this won't work in this case, beacuse of scope limiting factors - the EXEC cannot 'see' the @PERIOD_XXX variables, so it will throw an error.
Instead, you will need to use sp_executeSQL, and pass in all the parameters, and it will then update the correct one.

try something like this : (as I say, it is very messy, and long-winded, but it will meet your needs)

declare @SQL nvarchar(4000)
set @SQL = N'SET @PERIOD_' + cast(@PERIODCOUNT as nvarchar(5)) + ' = ''' + cast(@CURRENTPERIOD as nvarchar(5))+ ''''

exec sp_executesql @SQL,
N'@PERIOD_2 VARCHAR(20) OUTPUT,@PERIOD_3 AS VARCHAR(20) OUTPUT,@PERIOD_4 AS VARCHAR(20) OUTPUT,
@PERIOD_5 AS VARCHAR(20) OUTPUT,@PERIOD_6 AS VARCHAR(20) OUTPUT,@PERIOD_7 AS VARCHAR(20) OUTPUT,
@PERIOD_8 AS VARCHAR(20) OUTPUT,@PERIOD_9 AS VARCHAR(20) OUTPUT,@PERIOD_10 AS VARCHAR(20) OUTPUT,
@PERIOD_11 AS VARCHAR(20) OUTPUT,@PERIOD_12 AS VARCHAR(20) OUTPUT,@PERIOD_13 AS VARCHAR(20) OUTPUT',
@PERIOD_2 OUTPUT,@PERIOD_3 OUTPUT,@PERIOD_4 OUTPUT,
@PERIOD_5 OUTPUT,@PERIOD_6 OUTPUT,@PERIOD_7 OUTPUT,
@PERIOD_8 OUTPUT,@PERIOD_9 OUTPUT,@PERIOD_10 OUTPUT,
@PERIOD_11 OUTPUT,@PERIOD_12 OUTPUT,@PERIOD_13 OUTPUT
0
 
show_tAuthor Commented:
Thanks,

The simpler solution works best for Me.
0

Featured Post

Industry Leaders: 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!

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now