SQL concatenate variable in a while loop

Question

I have n number of @BLOCn variables.

Is it possible to concatenate a variable name so that one can use the loop counter as part of it? 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'm getting this error:

Conversion failed when converting the varchar value 'BLOC' to data type int.

I'm basically looking for the SQL equivalent of Javascript's:

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

Answer 1

You will not be able to do what you are asking the way that you are trying. SQL Server has an exec() function and an sp_executesql stored procedure that can run dynamic SQL. However, they both create another context for running the command.

If you are willing to use a table variable to hold your @BLOC values you can do something like this:

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