CODEWITHSUNDEEP
sqlsql-server-2008t-sql
greener
greener

Reputation: 5069

SQL concatenate variable in a while loop

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);

Upvotes: 5

Views: 6647

Answers (2)

dana
dana

Reputation: 18105

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

Upvotes: 3

logixologist
logixologist

Reputation: 3834

As Gordon Linoff said you can do this with dynamic SQL:

Here is the concept. This is fairly complete code but you will obviously need to tweek it to your requirements. I am doing a simple example of how dynamic sql works.:

Declare a variable to store your SQL in it:

 DECLARE @sql as varchar(max)

Then all you do is create a SQL statement but as a string like this. This will give you all your fields dynamically created:

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

Now understand this was not meant to be a complete solution but rather an explanation of how dynamic SQL works and how it can be applied to your issue.

In this case the SQL will now be:

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

Hope this helps!

Upvotes: -1

Related Questions