SQL concatenation variable in while loop

Question

I have n number of variables @BLOCn.

Is it possible to combine the variable name so that you can use the loop counter as part of this? For example:

DECLARE @BLOC1 int, @i int, @MAX int, @QTY int;

SET @i = 1;
SET @MAX = 1;
SET @BLOC1 = 12;

WHILE @i <= @MAX
BEGIN
   SET @QTY = FLOOR('BLOC'+@i)
   ...
END

SELECT @QTY

So far I am getting this error:

Conversion error when converting varchar 'BLOC' value to data of type int.

I am basically looking for the SQL equivalent of Javascript:

var foo = 'var';
var bar = 'Name';
window[foo + bar] = 'hello';
alert(varName);

Answer 1

You cannot do what you ask, as you try. SQL Server has a function exec()and a stored procedure sp_executesqlthat can run dynamic SQL. However, they create a different context for running the command.

@BLOC, - :

DECLARE @BLOCS table(k int, v int);
DECLARE @i int, @MAX int, @QTY int;

SET @i = 1;
SET @MAX = 1;

insert into @BLOCS values(1, 12)

WHILE @i <= @MAX
BEGIN
   SET @QTY = FLOOR((select v from @BLOCS where k = @i))
   set @i = @i + 1
END

SELECT @QTY

Answer 2

, SQL:

. , , , . , sql.:

SQL :

 DECLARE @sql as varchar(max)

, , SQL, , . :

declare @sqlcounter as int
declare @listofvariables as varchar(500)
set @sqlcounter =1

while sqlCounter <= 12
BEGIN
set @listofvariables = @listofvariables + 'BLOC' + @SqlCounter +', '
set @sqlCounter = @sqlCounter + 1
END

set @sql = 'select ' + @listofvariables + ' FROM tablename'

EXEC @SQL

, , , SQL .

SQL :

SELECT BLOC1, BLOC2, BLOC3, BLOC4, BLOC5, BLOC6, BLOC7, BLOC8, BLOC9, BLOC10, BLOC11, BLOC12 FROM tablename

, !