• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 343
  • Last Modified:

TSQL Variable Reference

Hello All,

Have a problem that I am hoping someone could help with. I have  stored procedure where I am assigning column values to individual variables as in the following manner

@c0 = Column A
@c1 = Column B
@c2 = Column C .... etc

In a while loop I want to reference the variables, singularly, but without naming each variable so I tried to construct a pointer (in a sense):
SELECT @Component = '@c' + CONVERT(nvarchar(5), @tCounter)
This however gives me the string literal "@c1" or "@c2" (or whatever @tCounter is at the moment)
So my question is, is it possible to refer to a variable without having to actually typing it out.
To be clearer, I am trying to flatten out a row into individual rows, per column.
Thank you all for your considerations and time.
0
ffowler1
Asked:
ffowler1
  • 2
  • 2
1 Solution
 
JestersGrindCommented:
You should probably use a table variable for what you are trying to do.  

DECLARE @c TABLE(RowID INT,  Value NVARCHAR(5))

Then in your loop assign the value like this.

SELECT @Component = Value FROM @c WHERE RowID = @tCounter.

Greg

0
 
ffowler1Author Commented:
Greg,

Thanks for the time answering, but I must have not been as clear as I should have been. I am actually already grabbing a row for a table, I want to access the fields within that row so that I may insert the individiual fields on it's own separate row in another table.

Thoughts anyone?
0
 
JestersGrindCommented:
I still think that there is probably a set based solution, but I don't know enough about the code to suggest one.  To do what you are trying to do, you have to use dynamic SQL.  Take a look at this code.

DECLARE @SQL NVARCHAR(500)

Then inside your loop.

SET @SQL = 'SELECT @Component = @c' + CONVERT(nvarchar(5), @tCounter)

EXECUTE sp_executesql @SQL

You dynamically create the variable assignment and then execute it.

Greg

0
 
ffowler1Author Commented:
It was a great starting point from where I could complete the rest on my own.
Thank you very much.
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.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

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