Convert numbered variables into a table row

Question

I'm working with a legacy system where a stored procedure is called and dozens (literally) of variables are passed in. The data needs to be processed the same way but currently I have to copy/paste the code with a numerical change dozens of times.

What I want to do is put the data into a table variable and use that to create a loop that runs the same code but with a different row from the table.

So, in summary, I have variables

TypeA1,
TypeB1,
TypeC1,
TypeD1,
etc.

That I want to store temporarily as

Index	TypeA	TypeB	TypeC	TypeD
1	TypeA1	TypeB1	TypeC1	TypeD1
2	TypeA2	TypeB2	TypeC2	TypeD2

This should allow me to loop, pull a row into generic variables, and execute the repeated process.

---

Do you mean that you'd process values (1, 'TypeA1'), (1, 'TypeB1'), (2, 'TypeA1') ... in the executed loop?

No, more process it as (1, TypeA, TypeB, SomeValue, AnotherThing). I have a hundred of these to process, but I get the values separately and only the numeric value of the variable tells me which one it is.

I have lots of variables that are only grouped by number. I need to be able to do the same task to each numbered group of variables i.e.

insert into X 
values (1, TypeA, TypeB, TypeC, TypeD, Something, AnotherThing)

The calling app can't do the loop because it is old and a black box. I can't get under the hood.

Answer 1

One option is to use a table value constructor

see https://learn.microsoft.com/en-us/sql/t-sql/queries/table-value-constructor-transact-sql?view=sql-server-ver16

for official documentation

This seems to fit what you are describing but if you have hundreds of variables you will end up writing (or scripting) lots of code, as far as I know there is no way to access metadata about variables but if there is a fixed pattern you might find building some formulas in a spreadsheet will help you produce the repetitive code.

declare @a1 sysname, @b1 int, @a2 sysname, @b2 int
select @a1 = N'row', @b1 = 42, @a2 =N'constructor', @b2 = 9
select identity(int, 1,1) as id, aaa, bbb
into #x
from (values(@a1, @b1), (@a2, @b2)) as x(aaa,bbb)
select  * from #x