SQL Server Performance

WHILE LOOP not ending

Discussion in 'SQL Server 2005 General Developer Questions' started by starwarsbigbang, Sep 14, 2008.

  1. starwarsbigbang New Member

    Can anyone figure out why this WHILE loop never ends.
    drop table #temp1
    drop table #temp3
    create table #temp1(title varchar(50),const int,timestamp datetime)
    CREATE TABLE #TEMP3(TITLE VARCHAR(50),const INT,TIMESTAMP DATETIME)

    insert into #temp1
    values ('MCCainCampaigns1',-101,'21-Aug-2008')
    insert into #temp1
    values ('MCCainCampaigns2',-101,'21-Aug-2008')
    insert into #temp1
    values ('MCCainCampaigns3',-101,'21-Aug-2008')
    insert into #temp1
    values ('MCCainCampaigns4',-101,'21-Aug-2008')
    declare @tempstrconst varchar(50)
    set @tempstrconst=(select top 1 title from #temp1)
    while @@rowcount<>0
    begin
    update #temp1
    set intconst=intconst-1
    where title=@tempstrconst

    insert into #temp3
    select * from #temp1
    where title='MysterySeries_Vol1'

    delete from #temp1 where title='MysterySeries_Vol1'
    set @tempstrconst=(select top 1 title from #temp1)
    end
    --Desired output:
    select * from #temp3
    'MCCainCampaigns1',-102,'21-Aug-2008'
    'MCCainCampaigns2',-103,'21-Aug-2008'
    'MCCainCampaigns3',-104,'21-Aug-2008'
    'MCCainCampaigns4',-105,'21-Aug-2008'
  2. SQL2000DBA New Member

    Hi,
    WHILE loop is running infinite because of SET condition "set @tempstrconst=(select top 1 title from #temp1)". @@ROWCOUNT Returns the number of rows affected by the last statement and after executing "set @tempstrconst=(select top 1 title from #temp1) ", @@ROWCOUT changes to 1 which means TRUE WHILE condition.
  3. Madhivanan Moderator

    No While Loop
    It is as simple asinsert
    into #temp3
    select title,const-row_number ()over(order by title) as consts,timestamp from #temp1select
    * from #temp3

Share This Page