Results 1 to 2 of 2

Thread: Cursor Help

  1. #1
    Join Date
    Sep 2003
    Posts
    3

    Unanswered: Cursor Help

    Hi,

    Can somebody help me with this code. I have a table of 1000 records wiith unque key Contactid. This table has some duplicates on First, Name and ZipCode. What I am trying to do is open a cursor with the name ContactsDedup and if it is the first record insert it into another table just contactid and personid(It is just some value so i am giving it from my row counter @counter. When my cursor goes to next record I am supposed to check if it is the same record as the first an dif it is look for ts personid in the other table and give the same personid to that contact also, if not then insert the that contactid with new personid(which I can use @counter).

    After writing this code I am getting as syntax error near else.

    Can somebody help me with this. I really willl appreciate I


    Create Procedure sp_ContactsDedupProcedure as
    Declare @Cid int
    Declare @FName varchar(100)
    Declare @LName varchar(100)
    Declare @ALine varchar(100)
    Declare @CC varchar(50)
    Declare @St varchar(50)
    Declare @Zip varchar(50)
    Declare @WA varchar(50)
    Declare @WP varchar(50)
    Declare @HA varchar(50)
    Declare @HP varchar(50)
    Declare @Email varchar(50)
    Declare @Login varchar(50)
    Declare @PID int
    Declare @Counter int
    Declare @RowCounter int
    Declare @FID int
    Declare @PPID int
    Set @counter = 0
    set @rowcounter = 0


    Declare ContactsDedup Cursor
    Scroll Dynamic For
    Select Contactid, FirstName, LastName, AddressLine1, City, StateOrProvince, PostalCode, WorkArea, Workphone,
    HomeArea, HomePhone, EmailAddress, LoginID, Personid from ContactsSample


    Declare @Contactid Int
    Declare @FirstName varchar(100)
    Declare @LastName varchar(100)
    Declare @AddressLine1 varchar(100)
    Declare @City varchar(50)
    Declare @Stateorprovince varchar(50)
    Declare @PostalCode varchar(50)
    Declare @WorkArea varchar(50)
    Declare @WorkPhone varchar(50)
    Declare @HomeArea varchar(50)
    Declare @HomePhone varchar(50)
    Declare @EmailAddress varchar(50)
    Declare @LoginID varchar(50)
    Declare @Personid varchar(50)



    Declare @Contactid1 Int
    Declare @FirstName1 varchar(100)
    Declare @LastName1 varchar(100)
    Declare @AddressLine11 varchar(100)
    Declare @City1 varchar(50)
    Declare @Stateorprovince1 varchar(50)
    Declare @PostalCode1 varchar(50)
    Declare @WorkArea1 varchar(50)
    Declare @WorkPhone1 varchar(50)
    Declare @HomeArea1 varchar(50)
    Declare @HomePhone1 varchar(50)
    Declare @EmailAddress1 varchar(50)
    Declare @LoginID1 varchar(50)
    Declare @Personid1 varchar(50)

    Declare PersonContacts Cursor For
    Select Contactid, Personid

    Declare @PContactId int
    Declare @PPersonID int


    open ContactsDedup

    Fetch Next from ContactsDedup into
    @Contactid, @FirstName, @LastName, @AddressLine1, @City, @StateOrProvince, @PostalCode, @WorkArea, @Workphone,
    @HomeArea, @HomePhone, @EmailAddress, @LoginID, @Personid


    While @@Fetch_Status = 0
    Begin

    set @Counter = @counter + 1

    If @Counter = 1

    Insert into PersonContactID(Personid, Contactid)
    select @counter, @contactid

    else



    set @counter = @counter + 1
    set @Cid = @contactid
    set @FName = @FirstName
    Set @LName = @LastName
    Set @ALine = @AddressLine1
    set @CC = @City
    set @St = @Stateorprovince
    set @zip = @PostalCode
    set @WA = @WorkArea
    set @WP = @WorkPhone
    set @HA = @HomeArea
    set @HP = @HomePhone
    Set @Email = @EmailAddress
    Set @Login = @loginid
    set @Pid = @Personid

    Declare ContactsDedupCheck Cursor For
    Select Contactid, FirstName, LastName, AddressLine1, City, StateOrProvince, PostalCode, WorkArea, Workphone,
    HomeArea, HomePhone, EmailAddress, LoginID, Personid from ContactsSample

    open ContactsDedupCheck

    Fetch Next from ContactsDedupCheck into @Contactid1, @FirstName1, @LastName1, @AddressLine11, @City1, @StateOrProvince1, @PostalCode1, @WorkArea, @Workphone1,
    @HomeArea1, @HomePhone1, @EmailAddress1, @LoginID1, @Personid1

    While @rowcounter >= @counter
    Begin

    set @rowcounter = @rowcounter + 1

    If @FName = @FirstName

    --If @Lname = @LastName

    --If @ALine = @AddressLine1

    Set @FID = @Contactid1

    else


    Fetch Next from ContactsDedupCheck into @Contactid1, @FirstName1, @LastName1, @AddressLine11, @City1, @StateOrProvince1, @PostalCode1, @WorkArea, @Workphone1,
    @HomeArea1, @HomePhone1, @EmailAddress1, @LoginID1, @Personid1
    End

    Open PersonContacts
    Fetch Next from ContactsPerson into @Contactid , @Personid
    While @@Fetch_Status = 0
    Begin
    If @FId = @Contactid

    set @PPId = @personid
    Insert into PersonContacts(Contactid, Personid)
    select @CID, @PPID
    else
    Insert into PersonContacts(Contactid, Personid)
    select @CID, @counter
    Fetch Next from ContactsPerson into @Contactid , @Personid

    End


    Fetch Next from ContactsDedup into
    @Contactid, @FirstName, @LastName, @AddressLine1, @City, @StateOrProvince, @PostalCode, @WorkArea, @Workphone,
    @HomeArea, @HomePhone, @EmailAddress, @LoginID, @Personid


    End




    Close ContactsDedup
    Deallocate ContactsDedup

    Close ContactsDedupCheck
    Deallocate ContactsDedupCheck

    Close PersonContacts
    Deallocate PersonContacts


    I will appreciate any help. This project d due today, please help as i am a novice to this.

  2. #2
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    Check this part:


    If @FId = @Contactid
    -- begin --- I guess you have to add begin
    set @PPId = @personid
    Insert into PersonContacts(Contactid, Personid)
    select @CID, @PPID
    --end --- the same
    else
    Insert into PersonContacts(Contactid, Personid)
    select @CID, @counter
    Fetch Next from ContactsPerson into @Contactid , @Personid

    End

Posting Permissions

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