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

# 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
• 3
• 2
1 Solution

Commented:
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

Author Commented:
Thanks mcmonap,

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

Thank-you
0

Commented:
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

Commented:
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

Commented:
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

Author Commented:
Thanks,

The simpler solution works best for Me.
0

## Featured Post

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