Convert cursor store proc TO non cursor

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Convert cursor store proc TO non cursor

Author

Topic

abarsami
Yak Posting Veteran

68 Posts

Posted - 2001-06-25 : 23:09:21

I have written a store procedure using cursor... but it takes way way to long. Is there a way to write one without using cursor.

----------------------
CREATE PROCEDURE dbo.Hotel
AS
declare @sprName varchar(80)
declare @datefuture datetime
declare @CurrentDate datetime
declare @hotel_code varchar(20)
declare @hotel_srp_d datetime
declare @hotel_srp_code varchar(20)
declare @hotel_srp_name varchar(80)
declare @hotel_room_qty int
declare @hotel_rev_amt money
declare @mcat_code varchar(20)
declare @hope varchar(20)
set nocount on

Declare NCursor Cursor For

select
hotel_code, hotel_srp_d, hotel_srp_code, hotel_srp_name,
hotel_room_qty, hotel_rev_amt, mcat_code
from
TableC
where
mgi_code in ('G','P') and acct_code is null

Open NCursor

Fetch next from NCursor Into @hotel_code, @hotel_srp_d, @hotel_srp_code, @hotel_srp_name, @hotel_room_qty, @hotel_rev_amt, @mcat_code

While(@@fetch_status <> -1)
BEGIN
if (@CurrentDate is null)
BEGIN

insert into Hotel_Group(hotel_code,mcat_code,group_post_as_name,
group_from_d)
values (@hotel_code,@mcat_code,@hotel_srp_name,@hotel_srp_d)

END

if ((@hotel_srp_d <> @CurrentDate + 1) or (@hotel_srp_name <> @sprName)) and (@CurrentDate is not null)
BEGIN
insert into Hotel_Group(hotel_code,mcat_code,group_post_as_name,
group_from_d)
values (@hotel_code,@mcat_code,@hotel_srp_name,@hotel_srp_d)

select @hope = hotel_group_code from Hotel_group where group_from_d = @hotel_srp_d
and group_post_as_name = @hotel_srp_name

update Hotel_group set group_to_d = @CurrentDate where group_post_as_name = @sprName
and hotel_group_code = (@hope-1)
END

select @sprName = @hotel_srp_name
select @CurrentDate = @hotel_srp_d

Fetch next from NCursor Into @hotel_code, @hotel_srp_d, @hotel_srp_code, @hotel_srp_name, @hotel_room_qty, @hotel_rev_amt, @mcat_code

END
Close NCursor
DeAllocate NCursor
-------------------------

One important thing:
@sprName & @CurrentDate contain the old values of name and date until the last two lines:
select @sprName = @hotel_srp_name
select @CurrentDate = @hotel_srp_d
Which update the @sprName & @CurrentDate variables.

I started to write a store procedure without using a cursor, but I don't know how to pass the @sprName and @CurrentDate variables.

Can someone help?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources