Well, you're initializing set @colname=RTRIM(LTRIM('W'+RTRIM(LTRIM(CONVERT(NVARCHAR(2),@COUNT)))))and then overwriting it in your update (set @colname ='PM') so I'm not really sure what you're trying to do.
---------------------------------------------------------------------TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLYTo post a dba job: http://jobs.lazydba.comTo subscribe : http://www.LazyDBA.comTo unsubscribe: http://www.lazydba.com/unsubscribe.html



> To: [Email address protected] From: mssqldba-ezmlmshield-x45173791.[Email address protected] Date: Mon, 31 Mar 2008 15:44:09 -0400> Subject: dynamic column update loop> > I need to update a table where the columns correspond to the weeks of the> year. I’ve written this loop which executes with no errors, but nothing gets> populated in the table. If I replace the variables with hard data, it> updates as expected. What did I do wrong – it has to do with the variable> for the column name I’m convinced, but when I do a select @colname it> returns the correct value, so I’m stumped.> > > > > > declare @colname char(5), @week INT, @count int> > > > set @count=1> > > > WHILE @COUNT<=52 BEGIN> > > > set @colname=RTRIM(LTRIM('W'+RTRIM(LTRIM(CONVERT(NVARCHAR(2),@COUNT)))))> > set @week=@count> > > > > > update iidata set @colname ='PM'> > from iidata join svc00200 on iidata.adrscode=svc00200.adrscode> > join svc00201 s2 on svc00200.callnbr=s2.callnbr> > where datepart(ww, svc00200.compdte) =@week> > and s2.probcde in ('pm', '8-A')> > > > SET @COUNT=@COUNT+1> > > > END> > > > > > > > Margaret Norkett> > Database Administrator> > Transaction Technology Corporation> > 55 Beattie Place Suite 410> > Greenville, SC 29602> > 864-382-3071 (office)> > 864-270-2884 (cell)> > > > > No virus found in this outgoing message.> Checked by AVG. > Version: 7.5.519 / Virus Database: 269.22.1/1352 - Release Date: 3/31/2008> 10:13 AM> > > > ---------------------------------------------------------------------> TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY> To post a dba job: http://jobs.lazydba.com> To subscribe : http://www.LazyDBA.com> To unsubscribe: http://www.lazydba.com/unsubscribe.html>
---------------------------------------------------------------------TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLYTo post a dba job: http://jobs.lazydba.comTo subscribe : http://www.LazyDBA.comTo unsubscribe: http://www.lazydba.com/unsubscribe.html
_________________________________________________________________
Windows Live Hotmail is giving away Zunes.
http://www.windowslive-hotmail.com/ZuneADay/?locale=en-US&ocid=TXT_TAGLM_Mobile_Zune_V3
MS Sql Server LazyDBA home page