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

@GordonLinoff I added some details above. How could I achieve that using dynamic SQL? — greener
– greener, Commented Jan 25, 2014 at 0:18
Instead of having n variables have a table variable with n rows. What are you actually trying to do? The code you have shown repeatedly assigning to @QTY and never using the assignment doesn't make much sense. — Martin Smith
– Martin Smith, Commented Jan 25, 2014 at 0:18
@MartinSmith the code above is a boiled down example of my problem. — greener
– greener, Commented Jan 25, 2014 at 0:25
And following your edit what are you actually trying to do? What do you need this for? You are asking about how to implement your proposed solution to something without telling us what the something is. There is no direct equivalent of that JS construct in SQL. — Martin Smith
– Martin Smith, Commented Jan 25, 2014 at 0:27

dana · Accepted Answer · 2014-01-25 02:19:59Z

3

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

edited Jan 25, 2014 at 2:19

answered Jan 25, 2014 at 0:50

dana

18.3k7 gold badges71 silver badges91 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

logixologist · Accepted Answer · 2014-01-25 00:45:51Z

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!

Collectives™ on Stack Overflow

SQL concatenate variable in a while loop

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related