how can i use the variable as column name

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

Transact-SQL (2000)

how can i use the variable as column name

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-07-13 : 10:18:06

mudassar writes "hi
I wanna an urgent response from anyone
I am trying to use the variable @v_test as column name which it select at run time from another table,but it provides me only the column name in return not the original value against the query here i am posting the procedure which I developed
thanks sql team bye see answer from any member within couple of days bye

////////////////////////////////////////////////////////////////
CREATE PROCEDURE dbo.call_zone
as declare @v_code1 varchar(10),@v_code2 varchar(10),@v_counter numeric,@v_test1 varchar(15),@v_test2 varchar(15);

DECLARE emp3 CURSOR

FOR SELECT code1,code2,counter
FROM feb04_dtli_guj;

BEGIN
OPEN emp3;
FETCH next from emp3 INTO @v_code1,@v_code2,@v_counter;
while @@fetch_status = 0

Begin
-- print @v_code1;

-- print @v_counter;

set @v_test1 = (select title from lhr_codes_original where city_code = @v_code2)

update feb04_dtli_guj
set call_zone=(select @v_test1 from lhr_codes_test where city_code = @v_code1)

where counter=@v_counter and call_zone is null;

FETCH next from emp3 INTO @v_code1,@v_code2,@v_counter;
ENd

CLOSE emp3
DEALLOCATE emp3
end
GO
////////////////////////////////////////////////////////////////"

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2004-07-13 : 11:01:03

quote:


////////////////////////////////////////////////////////////////
CREATE PROCEDURE dbo.call_zone
 as  declare @v_code1  varchar(10),@v_code2  varchar(10),@v_counter   numeric,@v_test1  varchar(15),@v_test2   varchar(15);

	
DECLARE emp3 CURSOR

     FOR SELECT     code1,code2,counter
     FROM   feb04_dtli_guj;
     

BEGIN
OPEN emp3; 
FETCH next from emp3 INTO  @v_code1,@v_code2,@v_counter;
	while  @@fetch_status = 0
	
	Begin
           -- print @v_code1;
            
           -- print @v_counter;
      
             set @v_test1 = (select   title   from   lhr_codes_original    where     city_code = @v_code2)
                
            update    feb04_dtli_guj
            set  call_zone=(select    @v_test1    from   lhr_codes_test    where     city_code = @v_code1)
        	
            where     counter=@v_counter   and  call_zone  is   null;
	     	
	 FETCH next from emp3 INTO @v_code1,@v_code2,@v_counter;
	 ENd

CLOSE emp3
DEALLOCATE emp3
end
GO
////////////////////////////////////////////////////////////////"

replace the code in red with the following :

quote:


////////////////////////////////////////////////////////////////
CREATE PROCEDURE dbo.call_zone
 as  declare @v_code1  varchar(10),@v_code2  varchar(10),@v_counter   numeric,@v_test1  varchar(15),@v_test2   varchar(15);


declare @SQL varchar(2000)
set @SQL = ''

	
DECLARE emp3 CURSOR

     FOR SELECT     code1,code2,counter
     FROM   feb04_dtli_guj;
     

BEGIN
OPEN emp3; 
FETCH next from emp3 INTO  @v_code1,@v_code2,@v_counter;
	while  @@fetch_status = 0
	
	Begin
           -- print @v_code1;
            
           -- print @v_counter;
      
             set @v_test1 = (select   title   from   lhr_codes_original    where     city_code = @v_code2)
                set @SQL = @SQL +	'update feb04_dtli_guj ' + 
			'set call_zone=(select ' + @v_test1  + ' from lhr_codes_test where city_code = ' + @v_code1 + ')'

print @SQL
exec(@SQL)
set @SQL = ''


                 	
            where     counter=@v_counter   and  call_zone  is   null; <---- What is this line doing here?!!!! 
	     	
	 FETCH next from emp3 INTO @v_code1,@v_code2,@v_counter;
	 ENd

CLOSE emp3
DEALLOCATE emp3
end
GO
////////////////////////////////////////////////////////////////"

------------->>> BREAKING NEWS!!! <<<-------------

Saddam Hussien has weapons of mass destrcution

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2004-07-13 : 11:46:23

Even more important...look to get rid of the cursor...the performance will be v.poor compared to a SET-BASED technique...you are updating the table 1 record at a time....when SQL has the capability to UPDATE every relevent record in the one go.

as has been said before...a bit like filling a tea-cup with sugar one-grain at a time using a tweezers...instead of using a spoon!!!

I know with the current problem you are constrained by a poor database design....having to chane columns depending on a data value points to this....but this might be the reason to improve that design.

mr_mist
Grunnio

1870 Posts

Posted - 2004-07-13 : 11:50:38

mmm, dynamic SQL and a cursor. Nice. ;)

-------
Moo. :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources