concatenation of column variable

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

concatenation of column variable

Author

Topic

skillile
Posting Yak Master

208 Posts

Posted - 2001-11-30 : 13:39:12

I have been trying this for some time but now I think
I can ask it right.

I am trying to combine two variables to make a dynamic colum
name. Can it be done? Here is my code

DECLARE @offstep int
DECLARE @lev1 varchar(10)
DECLARE @lev2 varchar(10)
DECLARE @lev3 varchar(10)
DECLARE @lev4 varchar(10)
DECLARE @lev5 varchar(10)
DECLARE @lev6 varchar(10)
DECLARE @lev7 varchar(10)
DECLARE @lev8 varchar(10)
DECLARE @lev9 varchar(10)
DECLARE @lev10 varchar(10)
DECLARE @x varchar(20)
DECLARE @str varchar(100)

SET @offstep = 6
SET @x=1

SET @lev1='%'
SET @lev2='%'
SET @lev3='%'
SET @lev4='%'
SET @lev5='%'
SET @lev6='%'
SET @lev7='%'
SET @lev8='%'
SET @lev9='%'
SET @lev10='%'

while @x <= @offstep
begin

set @str= 'select * from tbloffice where level'+ @x + ' like '+ (@lev + @x)
exec(@str)

SET @x=@x+1

end

slow down to move faster...

shankarc
Starting Member

37 Posts

Posted - 2001-11-30 : 14:12:56

Sure it can be done. But take care of the quotes when you use dynamic SQL, this is the irritating part when you use dynamic query string.

skillile
Posting Yak Master

208 Posts

Posted - 2001-11-30 : 14:20:20

this is the line I am having trouble with:

SQL says it can't find the variable @lev. How do I join the two variables
together?

set @str= 'select * from tbloffice where level'+ @x + ' like '+ (@lev + @x)

slow down to move faster...

ToddV
Posting Yak Master

218 Posts

Posted - 2001-11-30 : 14:45:55

I am not quite sure what you are going for here, but here is my advice. Print @Sql rather than exec. This way you will be able to figure out where you went wrong. If that does not sort things out for you, try to give us a better idea of what you are trying to accomplish.

skillile
Posting Yak Master

208 Posts

Posted - 2001-11-30 : 14:53:57

I did a print and it prints out exactly what I want but it would let
me assign the variable. Here is the code exactly how I have it. I want
to pull in ten (level) variables (1-10) and match them with the ten
level fields in the table. The offstep will return only return the levels
being used ie. this office is only using up to level 6. Hope that helps.

DECLARE @offstep int
DECLARE @lev1 varchar(10)
DECLARE @lev2 varchar(10)
DECLARE @lev3 varchar(10)
DECLARE @lev4 varchar(10)
DECLARE @lev5 varchar(10)
DECLARE @lev6 varchar(10)
DECLARE @lev7 varchar(10)
DECLARE @lev8 varchar(10)
DECLARE @lev9 varchar(10)
DECLARE @lev10 varchar(10)
DECLARE @x varchar(20)
DECLARE @str varchar(100)
SET @offstep = 6
SET @x=1
SET @lev1='%'
SET @lev2='%'
SET @lev3='%'
SET @lev4='%'
SET @lev5='%'
SET @lev6='%'
SET @lev7='%'
SET @lev8='%'
SET @lev9='%'
SET @lev10='%'
while @x <= @offstep
begin
set @str= 'select * from tbloffice where level'+ @x + ' like @lev' + @x
print (@str)
--exec(@str)
SET @x=@x+1
end

slow down to move faster...

ToddV
Posting Yak Master

218 Posts

Posted - 2001-11-30 : 15:15:14

Okay. Your problems lies in that the variables you declared are available in the context of the string execution. You can use a temp table to put the actual value in the code for @LevX.

DECLARE @offstep int
DECLARE @lev1 varchar(10)
DECLARE @lev2 varchar(10)
DECLARE @lev3 varchar(10)
DECLARE @lev4 varchar(10)
DECLARE @lev5 varchar(10)
DECLARE @lev6 varchar(10)
DECLARE @lev7 varchar(10)
DECLARE @lev8 varchar(10)
DECLARE @lev9 varchar(10)
DECLARE @lev10 varchar(10)
DECLARE @x varchar(20)
DECLARE @str varchar(2000)
SET @offstep = 6
SET @x=1
SET @lev1='%'
SET @lev2='%'
SET @lev3='%'
SET @lev4='%'
SET @lev5='%'
SET @lev6='%'
SET @lev7='%'
SET @lev8='%'
SET @lev9='%'
SET @lev10='%'

Create table #TEMP (
LevelNbr INT,
Value Varchar(50))

Insert #TEMP Values (1,@lev1)
Insert #TEMP Values (2,@lev2)
Insert #TEMP Values (3,@lev3)
Insert #TEMP Values (4,@lev4)
Insert #TEMP Values (5,@lev5)
Insert #TEMP Values (6,@lev6)
Insert #TEMP Values (7,@lev7)
Insert #TEMP Values (8,@lev8)
Insert #TEMP Values (9,@lev9)
Insert #TEMP Values (10,@lev10)

while @x <= @offstep
begin
set @str= 'select * from tbloffice where level'+ @x + ' like ' + (SELECT Value FROM #TEMP WHERE LevelNbr = @X)
print (@str)
--exec(@str)
SET @x=@x+1
end

--You could do this without the loop like so if your real code is this simple
SET @str = ''

SELECT @STR = @STR + 'select * from tbloffice where level' + LTRIM(LevelNbr) + ' LIKE ''' + Value + '''
'
FROM #TEMP

PRINT @str

skillile
Posting Yak Master

208 Posts

Posted - 2001-11-30 : 16:06:04

Ok, I created the table and got it to print right but I got an invalid column name
when I executed.

Here is what I did:

DECLARE @offstep int
DECLARE @lev1 varchar(10)
DECLARE @lev2 varchar(10)
DECLARE @lev3 varchar(10)
DECLARE @lev4 varchar(10)
DECLARE @lev5 varchar(10)
DECLARE @lev6 varchar(10)
DECLARE @lev7 varchar(10)
DECLARE @lev8 varchar(10)
DECLARE @lev9 varchar(10)
DECLARE @lev10 varchar(10)
DECLARE @x varchar(20)
DECLARE @str varchar(400)

DECLARE @temp table(levelint int, value nvarchar(50))

SET nocount on

SET @offstep = 6
SET @x=1

SET @lev1='USA'
SET @lev2='MI'
SET @lev3='West'
SET @lev4='%'
SET @lev5='%'
SET @lev6='%'
SET @lev7='%'
SET @lev8='%'
SET @lev9='%'
SET @lev10='%'

insert @temp values (1, @lev1)
insert @temp values (2, @lev2)
insert @temp values (3, @lev3)
insert @temp values (4, @lev4)
insert @temp values (5, @lev5)
insert @temp values (6, @lev6)
insert @temp values (7, @lev7)
insert @temp values (8, @lev8)
insert @temp values (9, @lev9)
insert @temp values (10, @lev10)

declare @test varchar(400)

set @test='select * from tblofficeprof where level1 like '+ (select value from @temp where levelint=1)

SET @x=@x+1
while @x <= @offstep
begin
set @str=' AND level'+ @x + ' like ' + (select value from @temp where levelint=@x)

set @test=@test+@str
print @test
--exec(@test)
SET @x=@x+1

end

slow down to move faster...

ToddV
Posting Yak Master

218 Posts

Posted - 2001-11-30 : 16:26:06

Sorry missedsome quotes. That was creating the strings:

where Level1 like %

We need
Where Level1 like '%'

To do this add two single quotes ('') arround the like. Here are the lines to replace:

set @test='select * from tblofficeprof where level1 like '''+ (select value from @temp where levelint=1) + ''''

SET @x=@x+1
while @x <= @offstep
begin
set @str=' AND level'+ @x + ' like ''' + (select value from @temp where levelint=@x) + ''''

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources