Results 1 to 4 of 4

Thread: @@sqlstatus

  1. #1
    Join Date
    Aug 2003
    Posts
    3

    Unanswered: @@sqlstatus

    whenever i added
    while @@sqlstatus = 0
    to my Sp it gives me an error that diallowed me to save my Sp_
    what can i do with it in order to check the status

    here part of my Sp_

    //Declare cursor
    DECLARE MeterInfoCursor CURSOR FOR
    SELECT count(*) as Count, Meter_ID, Meter_Reading, NoFlowDays, PeakBackFlow, Leak35Days, LeakStatus, No_Tamper, Tamper_Count, Read_Date, First_Time, Last_Time
    FROM MeterInfo
    GROUP BY Meter_ID, Meter_Reading, NoFlowDays, PeakBackFlow, Leak35Days, LeakStatus, No_Tamper, Tamper_Count, Read_Date, First_Time, Last_Time
    HAVING Count > 1
    ORDER BY Meter_ID, Meter_Reading, Read_date, First_Time, Last_Time
    for update;
    OPEN MeterInfoCursor;
    while @@sqlstatus = 0 FETCH NEXT MeterInfoCursor
    INTO Count, MainMeter_ID, MainMeter_Reading, MainNoFlowDays, MainPeakBackFlow, MainLeak35Days, MainLeakStatus,
    MainNo_Tamper, MainTamper_Count, MainRead_Date, MainFirst_Time, MainLast_Time;

  2. #2
    Join Date
    Jan 2003
    Posts
    26

    Re: @@sqlstatus

    Could be a few things here. First, you're fetching values into variables so you need to declare those variables and variable names must begin with @ signs. Second, you don't need semicolons after the statements. Third, and this may just be the way I'm used to doing it, I usually do a fetch then have a while loop with another fetch as the last step in the while loop:

    OPEN MeterInfoCursor
    FETCH MeterInfoCursor INTO @Count, @MainMeter_ID, @MainMeter_Reading, @MainNoFlowDays, @MainPeakBackFlow, @MainLeak35Days, @MainLeakStatus, @MainNo_Tamper, @MainTamper_Count, @MainRead_Date, @MainFirst_Time, @MainLast_Time

    while @@sqlstatus = 0
    begin
    ...
    FETCH MeterInfoCursor INTO @Count, @MainMeter_ID, @MainMeter_Reading, @MainNoFlowDays, @MainPeakBackFlow, @MainLeak35Days, @MainLeakStatus, @MainNo_Tamper, @MainTamper_Count, @MainRead_Date, @MainFirst_Time, @MainLast_Time
    end
    close MeterInfoCursor
    deallocate MeterInfoCursor

    Hope this helps.

    Frank

  3. #3
    Join Date
    Aug 2003
    Posts
    3

    @@sqlstatus

    the cursor you were looking is just a portion of it, i actually have everything you said. btw i should attached the complete sp, sorry for confusing
    Attached Files Attached Files

  4. #4
    Join Date
    Jun 2003
    Location
    Sydney, Australia
    Posts
    66

    Re: @@sqlstatus

    I'm not sure this was written for Sybase in the first place. It would be an interesting challenge to guess the type of database which could run this. Despite what people say ( including the introductory blurb in the SQL forum), there are significant differences between the procedural languages of various vendors.

    From what I know, Sybase has no Fetch NEXT just simple FETCH. The NEXT probably comes from MS Sql Server 2000, where it makes sense, because there are all kinds of cursors.

    Sybase has @@sqlstatus ( not SqlSatate ). But it canot be Sql 2000, because that one has @@fetch_status.

    Sybase would also have trouble with Declare Exception and the statement label followed by a semicolumn MeterInfoLoop: And the DECLARE LOCAL TEMPORARY TABLE TempMeterInfo is neither Sybase, not Oracle , not Sql Server. Use create #mytable(... ) instead

    More to the point, I can see your logic: first, store the groups into a temp table. By group I mean a record which needs to be deduplicated. Then, delete all rows from the main table, corresponding to the duplicates. Finally, insert back into the main table a single representative copy for each group.

    The proper ( and much faster) way to do this is with set-based processing. This means Sybase does the loops for you, much faster. You don't write any loop.

    create table #tbl (
    MainMeter_ID Varchar(10),
    MainMeter_Reading Varchar(10),
    MainFirst_Time Varchar(8),
    MainLast_Time Varchar(8) -- a comma after each column except the last one
    -- and so on
    )

    Begin Transaction -- heaven forbid we fail halway through

    insert #tbl ( MainMeter_ID, MainMeter_Reading, ... )
    ( select MainMeter_ID, MainMeter_Reading, ....
    from MeterInfo
    group by MainMeter_ID, MainMeter_Reading, ....
    having count (*) > 1
    )

    delete MeterInfo
    from MeterInfo mi, #tbl
    where mi.MainMeter_ID = #tbl.MainMeter_ID
    and mi.MainMeter_Reading = #tbl.MainMeter_Reading
    -- and so on for other columns

    insert MeterInfo
    ( MainMeter_id, MainMeter_Reading,...)
    ( select MainMeter_id, MainMeter_Reading,...
    from #tbl
    )

    COMMIT

    -- here, you can count the groups in #tbl

    # tbl drops automatically at the end of the stored proc

Posting Permissions

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