Results 1 to 12 of 12
  1. #1
    Join Date
    Nov 2004
    Location
    India
    Posts
    31

    Exclamation Unanswered: "Cursor-Fetch" problem:Oracle2SQL Server Migration

    Dear all,

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

    Code:
    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!
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~
    Knight says:

    You can't change the past, but you can ruin a perfectly good present by worrying about the future..

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    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:
    Code:
    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
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  3. #3
    Join Date
    Feb 2004
    Posts
    88
    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

  4. #4
    Join Date
    Nov 2004
    Location
    India
    Posts
    31

    Ok, But...

    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
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~
    Knight says:

    You can't change the past, but you can ruin a perfectly good present by worrying about the future..

  5. #5
    Join Date
    Feb 2004
    Posts
    88
    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/orac...ver/index.html

    Bill

  6. #6
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    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.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  7. #7
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Quote Originally Posted by thompbil
    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.
    Last edited by rdjabarov; 12-01-04 at 11:27.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  8. #8
    Join Date
    Nov 2004
    Location
    India
    Posts
    31

    Thumbs down C-h-i-n-k-s in SQL Srvr's armour!

    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?)
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~
    Knight says:

    You can't change the past, but you can ruin a perfectly good present by worrying about the future..

  9. #9
    Join Date
    Feb 2004
    Posts
    88
    Quote Originally Posted by KnightHasan
    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.

  10. #10
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Man, just wait till Yukon comes out, - talking about Horacle...
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  11. #11
    Join Date
    Nov 2004
    Location
    India
    Posts
    31

    Red face

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

    Duhh...?
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~
    Knight says:

    You can't change the past, but you can ruin a perfectly good present by worrying about the future..

  12. #12
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by KnightHasan
    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

Posting Permissions

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