"Cursor-Fetch" problem:Oracle2SQL Server Migration

Dear all,

I have a procedure in Oracle that contains the following cursor:

CURSOR SCHED_TRIPS IS
SELECT TRAVELDATE, STOP_NUM, TRIPID, STOP_TYPE, PROMISED_TIME, ETA, PERFORM_TIME, DEPART_TIME, ETD, DRIVERWAIT, PASSENGERWAIT, TRIPTIME, GROUP_ID
FROM Dbo.SCHEDTRIPS_VIEW
WHERE UNQ_ID = SESSION_ID AND TRUNC(TRAVELDATE) = TRUNC(TDATE)
AND DISPOSITION <> 'V';
BEGIN
FOR S IN SCH_TRIPS LOOP
UPDATE dbo.SCHEDULES T
SET T.DIRTYBIT = 1
WHERE T.TRIPID = S.TRIPID AND T.STOP_TYPE = S.STOP_TYPE AND (T.STOP_NUM <> S.STOP_NUM OR T.ETA <> S.ETA);

UPDATE dbo.SCHEDULES T
SET T.STOP_NUM = S.STOP_NUM, T.PROMISED_TIME = S.PROMISED_TIME, T.ETA = S.ETA, T.ETD = S.ETD, T.LAST_CHANGED = SYSDATE
WHERE T.TRIPID = S.TRIPID AND T.STOP_TYPE = S.STOP_TYPE;
END LOOP;
COMMIT ;
END;

My problem is with the line shown in Red. What will be the T-SQL equivalent for this line.

Anxiously waiting for help!Most common loop structure is:

while @.@.fetch_status = 0 begin
...
end

But you'll have to change your UPDATE statement to reference the variables that you're going to be FETCHing the values into, rather than referencing the fields from the cursor. Also, by looking at your JOINs you'll have to implement conditional UPDATE because values from the cursor will correspont to only 1 row at a time, while your current syntax suggests that the cursor now is used as a subquery which will not be possible in SQL. In other words it'll look something like this:declare @.stop_num int, @.tripid int, @.stop_type char(1), @.promised_time datetime, @.eta datetime, @.etd datetime
declare s cursor local for
select STOP_NUM, TRIPID, STOP_TYPE, PROMISED_TIME, ETA, ETD
from Dbo.SCHEDTRIPS_VIEW
where UNQ_ID = SESSION_ID AND convert(char(8), TRAVELDATE, 112) = convert(char(8), TDATE, 112)
open s
fetch next from s into @.stop_num, @.tripid, @.stop_type, @.promised_time, @.etd, @.etd
while @.@.fetch_status = 0 begin
update t
set t.STOP_NUM = @.stop_num,
t.PROMISED_TIME = @.promised_time,
t.ETA = @.eta,
t.ETD = @.etd,
t.LAST_CHANGED = current_timestamp,
t.DIRTYBIT = case when (t.STOP_NUM <> @.stop_num OR t.ETA <> @.eta) then 1 else t.DIRTYBIT end
from dbo.SCHEDULES t
where t.TRIPID = @.tripid AND t.STOP_TYPE = @.stop_type
fetch next from s into @.stop_num, @.tripid, @.stop_type, @.promised_time, @.etd, @.etd
end
deallocate s
close s|||But In this sort of case, native TSQL programmers probably wouldm't use a cursor at all. I would code:

UPDATE T
SET T.DIRTYBIT = 1
FROM dbo.SCHEDULES T,
Dbo.SCHEDTRIPS_VIEW S
WHERE T.TRIPID = S.TRIPID
AND T.STOP_TYPE = S.STOP_TYPE
AND (T.STOP_NUM <> S.STOP_NUM OR T.ETA <> S.ETA)
AND UNQ_ID = SESSION_ID
AND TRUNC(TRAVELDATE) = TRUNC(TDATE)

UPDATE T
SET T.STOP_NUM = S.STOP_NUM,
T.PROMISED_TIME = S.PROMISED_TIME,
T.ETA = S.ETA,
T.ETD = S.ETD,
T.LAST_CHANGED = SYSDATE
FROM dbo.SCHEDULES T,
Dbo.SCHEDTRIPS_VIEW S
WHERE T.TRIPID = S.TRIPID AND
T.STOP_TYPE = S.STOP_TYPE;
AND UNQ_ID = SESSION_ID
AND TRUNC(TRAVELDATE) = TRUNC(TDATE)

Bill|||Thx rdjabarov for going into the intricacies of my proc and giving a detailed reply.
But this was something which I was trying to avoid. Isn't there something similar to Oracle in SQL Server. Else I will have to declare hundreds of vars bcoz this is not the only proc with this style of code.
Moreover, shouldn't Close cursor statement come before deallocation?

Plz do suggest something to overcome my dilemma.

Thx again|||If you want to mimic the PL/SQL cursor style of updates in TSQL, I'm afraid there are no shortcuts.

As you'll be aware, the widespread use of cursors in ORACLE is unavoidable - that's just how you do things like updating one table from another. The particular syntax of the cursor loop in your example is neat PL/SQL shorthand to make cursor loops easier and quicker to code.

There is no equivalent to this shorthand in TSQL. You just have to do it the long way :(

In TSQL (in both MSSQL and Sybase) the use of cursors is widely discouraged, where avoidable. There is a significant overhead in using them that simply isn't there in ORACLE.

I don't know if this might be of some use to you...

http://www.swissql.com/products/oracle-to-sqlserver/index.html

Bill|||Actually the overhead associated with cursors also exists in Horacle. It's just the latter is usually run on monsterous hardware that can handle sloppy coding and poor design. SQL Server is running in prod environment on machines that are several times (sometimes a dozen or more) cheaper, and every intelligent attempt to optimize a process brings a reward in improved performance.|||But In this sort of case, native TSQL programmers probably wouldm't use a cursor at all...TSQL programmers would also rewrite it into 1 update and convert the Horacle style into ANSI ;)

I just tried to retain the structure as it was presented in the post, that also included the use of cursor.|||Thx guys for the tips,

rdjabarov, why "Horacle"?

thompbil, I have already used the link that u kindly pointed out. Didn't find the results satisfactory. Thx all the same. Another thing, besides the marginal loss in performance by using Cursors, what other overheads can I expect? Moreover, what cud be a substitute for cursors, if the overheads are significant?

Accepted that SQL Server is user friendly, but I think it is miles behind in "usefulness" as compared to Oracle. My original post is a case inpoint. Just imagine the lengths that I will have to go to achieve what has been accomplished so simply in Oracle.
Date functions of Oracle is another feather in Oracle's cap if we put these 2 RDBMSs head-to-head.

So, whatsay? (Is it a pandora's box I am opening here or what?)|||Thx guys for the tips,

Accepted that SQL Server is user friendly, but I think it is miles behind in "usefulness" as compared to Oracle. My original post is a case inpoint. Just imagine the lengths that I will have to go to achieve what has been accomplished so simply in Oracle.
Date functions of Oracle is another feather in Oracle's cap if we put these 2 RDBMSs head-to-head.

So, whatsay? (Is it a pandora's box I am opening here or what?)

They are just different. SQL Server does some things better than ORACLE. ORACLE does some things better than SQL Server.
You could say that the "UPDATE...FROM..." construct (as in my original reply) is even neater than the PL/SQL cursor update example you originally cited. I think so...but that's just an opinion.|||Man, just wait till Yukon comes out, - talking about Horacle...|||Yukon! Horacle! Whoa.. What? Who? When?

Duhh...?|||Yukon! Horacle! Whoa.. What? Who? When?

Duhh...?Yukon is the project name for the next version of SQL Server (either 9.0 or SQL 2005, depending on your point of view).

Horacle is an often used rdjabarovism for Oracle.

-PatP