Results 1 to 5 of 5

Thread: cursors

  1. #1
    Join Date
    May 2003
    Posts
    40

    Unanswered: cursors

    declare BadRecords cursor dynamic for select lngZipCodeID
    from ZipCodes where lngZipCode=@ZipCode and lngZipCodeID<>@NewZipID

    /* this is the syntax in Sybase
    open BadRecords;
    BadRecordsLoop: loop
    fetch next BadRecords into BadID;
    if sqlstate<>0 then
    leave BadRecordsLoop
    end if
    */

    I have to convert it to sql , I m just checking whether my syntax is correct or not

    open BadRecords
    while @@fetch_status = 0
    begin
    set @BadId = fetch next BadRecords
    if @@Error = 0
    end


    I have doubt in my syntax. Can you help me out in this?


    Thx

  2. #2
    Join Date
    Aug 2003
    Location
    Andover, MA
    Posts
    256
    open BadRecords
    fetch next from BadRecords INTO @BadId
    while @@fetch_status = 0
    begin
    ...do stuff...
    fetch next from BadRecords into @BadId
    end
    -bpd

  3. #3
    Join Date
    Aug 2003
    Location
    Andover, MA
    Posts
    256
    Oh, and most importantly, don;t forget to close and deallocate your cursor after you are done with it!!

    close BadId
    deallocate BadId
    -bpd

  4. #4
    Join Date
    May 2003
    Posts
    40
    bpd

    open curDupZips
    fetch next from curDupZips INTO BadId
    while @@fetch_status = 0
    begin
    if @@ERROR <> 0
    fetch next from BadRecords into BadId
    end
    close BadID
    Deallocate BadID


    Will this work? It says @BadId is not a cursor variable.

    My another imp question is instead of sqlstate can I use
    @@ERROR ??

    Another question is after @@ERROR checking I have to close it
    y again fetch next.......(two times)


    Thks

  5. #5
    Join Date
    Aug 2003
    Location
    Andover, MA
    Posts
    256
    Ooops. My bad. From the top....

    (you deal with the @ZipCode and @NewZipID stuff)
    ------------------------------------------------------------------
    DECLARE @BadId VARCHAR(12) -- 7+4 zip code

    DECLARE curDupZips CURSOR FOR
    SELECT lngZipCodeID FROM ZipCodes WHERE lngZipCode=@ZipCode AND lngZipCodeID<>@NewZipID

    FETCH NEXT FROM curDupZips INTO @BadID --Need to fetch an initial value before checking @@fetch_status

    WHILE @@FETCH_STATUS=0 --condition for looping
    BEGIN --beginning of loop
    <...Do Stuff...>
    FETCH NEXT FROM curDupZips INTO @BadId --Fetch next value
    END --end of loop

    CLOSE curDupZips
    DEALLOCATE curDupZips
    ------------------------------------------------------------------

    BTW, are you just trying to find duplicate zip codes in a table of zip codes? If so, it would be much more efficient to do it with a JOIN than a cursor.
    -bpd

Posting Permissions

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