How to loop troo records in stored procedure

[hlybbi]

i am trying to make the store proceture make an #temp table for me but i dont know how to read from other tables and loop throw records

ALTER PROCEDURE dbo.Date_stodulisti
@HotelID int,
@Gerd int,
@FromDate smalldatetime,
@ToDate smalldatetime
AS

declare @temp int
declare @RetValue int

SELECT @temp = id
FROM Herbergi_Gerd
WHERE hotel=@HotelID

// while (!.EOF) how can i make this possible

SELECT @RetValue=COUNT(*)
FROM herb_Action
WHERE herb_Action.gerd=@Gerd
AND herb_Action.bokunarNr NOT LIKE '%\001%'
AND herb_Action.Agency_Customers=0
AND herb_Action.dags_koma<=@FromDate
AND herb_Action.dags_fara>@ToDate
AND cheack_inn>=1
AND herb_Action.hotel=@HotelID

return @RetValue

END

Best regards Hlynur

http://www.xodus.net

 

[mrdenny]

You use a cursor to loop through records one at a time.

You will want to look under cursors in BOL. It gives a lot of information about them.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[TysonLPrice]

Here is an example of processing using a cursor. Probably not what you need but it might give you some ideas. It's running through one table matching up values to another. Loading the cursor and fetching through it is the main idea.

DECLARE Update_Cursor CURSOR
dynamic scroll_locks
FOR select country, state from locations 
        	
OPEN    Update_Cursor
Declare @Country as varchar(10)
Declare @State  as varchar(2)
declare @UpdateValue as varchar(10)
declare @recordCount as int
declare @msg as varchar(255)



FETCH   NEXT FROM Update_Cursor INTO @country, @state
set @UpdateValue = '  '

WHILE @@FETCH_STATUS = 0
BEGIN
 	
    set @updateValue = (select tlp_temp.country 
	                from tlp_temp 
                        where state = @state) 
    if @UpdateValue > '  '
	begin
	    update locations set country = @UpdateValue where current of Update_cursor
	    set @recordCount = @recordCount + 1
	end	 
    set @UpdateValue = '  '
    FETCH   NEXT FROM Update_Cursor INTO  @country,@state
END

 

[nikhilparchure]

Pls use the cursors if no other solution is available.
very much resource hungry
Use of #temp tables to loop through the records.
Also solutions are always available

Nikhil