If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > Microsoft SQL Server > How to loop through select statement using MS SQL?

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
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.
Reply With Quote
  #2 (permalink)  
Old
SQL Consultant
 
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 20,000
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
__________________
r937.com | rudy.ca
please visit Simply SQL and buy my book
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Apr 2012
Posts: 207
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.
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
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
How to loop through select statement using MS SQL?-sampledata.jpg  
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
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.
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
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
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On