Set variable using dynamic variable name

I am trying to set a variable using a dynamic name and recieving an error:

 SET @FID + CAST(@OPTION_CNT AS VARCHAR(1)) = @col1
davidpaezAsked:
Who is Participating?
 
Atlanta_MikeConnect With a Mentor Commented:
Actually it looks like he is trying to set @FID1 = @Col1 and @FID2 = col2 based upon an enumerated value of @OPTION_CNT.

What is your purpose david? Maybe we can help find a work around. Scott is right this is an array. A table variable is most likely your best solution.
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
this is not possible , if you wish to do this , you need to modify your entire script to use dynamic sql
0
 
Atlanta_MikeCommented:
SET @col1 = @FID + CAST(@OPTION_CNT AS VARCHAR(1))
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Aneesh RetnakaranDatabase AdministratorCommented:
                           
Atlanta_Mike,
i think he is looking to set something like

SET @Col1 =
SET @Col2 =
.something like this
0
 
Scott PletcherSenior DBACommented:
You can use either a table variable or a temp table to simulate an array.  As we all know, sadly there is no direct array support in T-SQL :( .
0
 
davidpaezAuthor Commented:
what I'm trying to do is assign values based on a results set stored in a cursor (I have other variables declared but I think this is the detail you are looking for).  I think a simple loop would also work but thought this way would be more effective.

DECLARE csrName CURSOR FOR
SELECT t1.request_id, t1.vocab, t1.menu_option
FROM tbl_f_fulfillment_def t1, tbl_f_fulfillment_def t2
WHERE t2.menu_option <= t1.menu_option and t1.active = 1
group by t1.menu_option, t1.request_id, t1.vocab

SELECT @OPTION_CNT = COUNT(*) FROM TBL_F_FULFILLMENT_DEF WHERE active = 1

OPEN csrName
FETCH NEXT FROM csrName INTO @col1, @col2, @col3
WHILE @@FETCH_STATUS = 0
BEGIN

           --SET @col1 = '@FID + (CAST(@OPTION_CNT AS VARCHAR(1)))' =
          -- SET @VOCAB2 = @col2
          -- SET '@OPTION['@OPTION_CNT']' = @col3

      --    @OPTION_CNT = @OPTION_CNT + 1

FETCH NEXT FROM csrName INTO @col1, @col2, @col3
END --WHILE
CLOSE csrName
DEALLOCATE csrName
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.