Results 1 to 6 of 6
  1. #1
    Join Date
    May 2012
    Posts
    16

    How to loop through select statement using MS SQL?

    What I wanted to do is I want to loop through each select result and at the same time use the result to do something while in a loop.

    While (select field1,field2,field3 from table 1)
    BEGIN
    select count(*) as field4 from table1 where field1(current_record)>3
    update table1 set field2(current_record)=field4
    END

    something like that...How do I do this?
    Any help would be greatly appreciated.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by jonelamora View Post
    What I wanted to do is I want to loop ...
    actually, looping is almost always a very bad idea

    looping is a method of achieving a certain result

    there are often other methods

    you did not really explain the result you want to achieve
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Apr 2012
    Posts
    213
    I think it would have been better if you had posted a sample data table and the expected result.
    From what I understand of your code I think it would look like this:

    Code:
    with CTE as
    (
        select COUNT(*) as Tot from Table1
        where field1 > 3
    )
    
    update Table1 
    set field2 = (select Tot from CTE)
    Hope this helps.

  4. #4
    Join Date
    May 2012
    Posts
    16
    I have uploaded a sample table to make things more clear.

    I know looping is a very bad idea. because it really makes everything slow.
    But what I need to achieve now seems to be different. It requires me to do loop.
    If you have seen the image I attached to this reply.

    I need to count how many postalNO are around 5km,10km radius of a certain km.
    to be able to do that what I did was I selected all the postal no. and then sequentially loop through them. to be able to count how may postalno. are around that certain postalno using their latitude and longitude.

    so basically what I did is like this.

    Code:
    select * postalno from table1
    
    do
    select (select count(*) from table1 where (distance_base on latitude and longitude)>=5) as 5kmcount from table1
    select (select count(*) from table1 where (distance_base on latitude and longitude)>=10) as 10kmcount from table1
    
    update table1 set 5km=5kmcount,10km=10kmcount from table1 where postalno=current_postalno.
    
    move_to_next_record
    while not end of postalno_record
    I did this using macro, but things are really so slow. to be able to update 1 record the code needs around 3 seconds, unfortunately I have 123000 records which means it would take days before it finishes. I thought the macro part slows it down so that is why I was trying to find if I can do such thing using SQL only. or is there any other way of doing this by not using loops. it loops 123000 times and counts 123000 records for 5km and another 123000 records for 10km and then update every loop.

    Any help would be very appreciated.
    Attached Thumbnails Attached Thumbnails sampledata.JPG  

  5. #5
    Join Date
    May 2012
    Posts
    16
    Thank you very much for all those who tried to help, I figured it out how to do it. This is the code I used.

    Code:
    DECLARE db_cursor CURSOR FOR Select Distinct top 1000 YuubinBangou,round(Latitude,5) as Latitude,round(Longitude,5) as Longitude from CompleteYuubinwithlatlon where fivekmcount is null; 
    DECLARE @myYuubinBangou int; 
    DECLARE @myLat float; 
    DECLARE @myLon float; 
    DECLARE @myFive int; 
    DECLARE @myTen int; 
    DECLARE @myFifteen int; 
    
    OPEN db_cursor; 
    FETCH NEXT FROM db_cursor INTO @myYuubinBangou, @myLat, @myLon; 
    WHILE @@FETCH_STATUS = 0  
    BEGIN  
    
    SELECT @myFive=COUNT(distinct sub.yuubinbangou) FROM (SELECT ((ACOS(SIN(@myLat * PI() / 180) * SIN(latitude * PI() / 180) + COS(@myLat * PI() / 180) * COS(latitude * PI() / 180) * COS((@myLon - longitude) * PI() / 180)) * 180 / PI()) * 60 * 1.1515) AS rangea,yuubinbangou FROM completeyuubinwithlatlon) sub WHERE sub.rangea<=3.1056 and sub.rangea>0; 
    
    SELECT @myTen=COUNT(distinct sub.yuubinbangou) FROM (SELECT ((ACOS(SIN(@myLat * PI() / 180) * SIN(latitude * PI() / 180) + COS(@myLat * PI() / 180) * COS(latitude * PI() / 180) * COS((@myLon - longitude) * PI() / 180)) * 180 / PI()) * 60 * 1.1515) AS rangeb,yuubinbangou FROM completeyuubinwithlatlon) sub WHERE sub.rangeb<=6.2112 and sub.rangeb>0; 
    
    SELECT @myFifteen=COUNT(distinct sub.yuubinbangou) FROM (SELECT ((ACOS(SIN(@myLat * PI() / 180) * SIN(latitude * PI() / 180) + COS(@myLat * PI() / 180) * COS(latitude * PI() / 180) * COS((@myLon - longitude) * PI() / 180)) * 180 / PI()) * 60 * 1.1515) AS rangec,yuubinbangou FROM completeyuubinwithlatlon) sub WHERE sub.rangec<=9.3168 and sub.rangec>0; 
    
    Update CompleteYuubinWithLatLon set fivekmcount=@myFive,tenkmcount=@myTen,fifteenkmcount=@myFifteen where Yuubinbangou=@myYuubinBangou 
    
    FETCH NEXT FROM db_cursor INTO @myYuubinBangou, @myLat, @myLon; 
    END; 
    CLOSE db_cursor; 
    DEALLOCATE db_cursor;
    YuubinBangou = PostalNo

    Thank you very much.

  6. #6
    Join Date
    Nov 2011
    Posts
    2
    I am not sure, but i think you need something like this.

    Code:
    Drop table test
    Go
    Create table test
    ([PostalNo] int,
    [Latitude] numeric(18,4),
    [Longtiude] numeric(18,4),
    [5KM] int,
    [10KM] int)
    Go
    Insert  into test values (12345,2,3,0,0)
    Insert  into test values (12346,10,20,0,0)
    Insert  into test values (12347,4,5,0,0)
    Insert  into test values (123,10,20,0,0)
    Insert  into test values (12,20,30,0,0)
    
    GO
    --Select * from test
    Drop PROCEDURE CursorSprocDemo
    GO
    CREATE PROCEDURE CursorSprocDemo
    AS
    		
    	DECLARE @PostalNo int
    	--DECLARE @SName nvarchar(50)
    
    	DECLARE DemoCursor CURSOR FOR 
    	SELECT [PostalNo]  FROM test  Order By [PostalNo]
    
    	OPEN DemoCursor
    
    	FETCH NEXT FROM DemoCursor INTO @PostalNo --, @SName
    
    	WHILE @@FETCH_STATUS = 0
    	BEGIN
    		--UPDATE test SET [Sname] =  [Sname] +  ' - ' + Convert(nvarchar(2),@ID)   ,[Marks]=30+@ID  where ID = @ID
             Declare @5KmCount int
             Declare @10KmCount int
             Select @5KmCount =SUM(case When [Latitude] Between 1 and 5 And [Longtiude] Between 1 and 5 then 1 else 0 end)  
    		 from test
             Where [PostalNo]=@PostalNo
             Group By [PostalNo]
    
             Select @10KmCount =SUM(case When [Latitude]>= 10 And [Longtiude] >=10 then 1 else 0 end)  
    		 from test
             Where [PostalNo]=@PostalNo
             Group By [PostalNo]
          
             Update test set [5KM]=@5KmCount from test where [PostalNo]=@PostalNo
    	     Update test set [10KM]=@10KmCount from test where [PostalNo]=@PostalNo
    
    		FETCH NEXT FROM DemoCursor INTO  @PostalNo --@ID, @SName
    	END
        Select * from test
    
    CLOSE DemoCursor
    DEALLOCATE DemoCursor
    GO
    CursorSprocDemo

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •