Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2007
    Posts
    29

    Red face Unanswered: Help on this sp, it should be PERFECT!!!!

    Hi there, could someone tell me what is the wrong with the below SP's CURSOR.I exec this sp through a table when update a filed value like this :
    EXECUTE THROUGH TRIGGER
    exec SP_ALOCATE_PAT_CREDIT @patid, @creditno, @totalamount, @userid, @sdesc ,@creDate,'C'

    When i run execute this sp, I got this error :
    ERROR:
    Another user has modified the contents of this table or view;the database row you are modifying no longer exists in the database.Databse error: '[Microsoft][ODBC SQL Server Driver][SQL Server]A cursor with the name 'INVOICE_LIST' does not exist.[Microsoft][ODBC SQL Server driver][SQL Server]The statement has terminated.'


    ==============================
    THANX GUYZ, ALREADY SOLVED IT FINALLY , ON TODAY EARLY MORNING.WELL, I GUES U GUYS R RITE, I NEED MORE PRACTISE BUT I DID GOOD AT THIS STAGE TOO EVENTOUGH IT IS NOT SO STANDARD..HAHA ,THNX ANYWAY, WILL WORK TO BE BETTER!
    Last edited by tommyboy1910; 04-20-07 at 14:17.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Wow. That is horrible.
    If you want this to be perfect, you have a helluva long way to go. As a matter of fact, you would be best off scrapping absolutely everything you have done so far and starting again from the beginning. Your whole design is conceptually flawed.
    You are using cursors unnecessarily, and you are calling this procedure from a trigger without any reference to what records it should act upon, and no handling for multi-record inserts either.
    First, rewrite your procedure using set-based operations instead of a cursor.
    Then, read the sections on triggers in Books Online until you understand the purpose and utility of the INSERTED and DELETED virtual tables.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Apr 2007
    Posts
    29
    Thanx for ur concern BLINDMAN, As for my level, i beleive this is what I can output as a totaly new to sql world.i need this to be set up as soon as posible.With minimum guide and help i gues this is what i can come so far.So i hope a guide or few examples would be greatly helpful rather thehn ur advise.thanx Mr BLINMAN eventhough u werent help me much

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Sorry tommy boy - you might not like it much but blindman is right. Apart from it being a flawed idea in the first place the execution is all wrong too. As a self confessed newby you would be well advised to read his post and think seriously about where to go from here. There are no hints, tips or tricks to sort it out - you need a wholesale redesign.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Apr 2007
    Posts
    29
    Oh Than Poo*, Then I shall consider it. God bless america

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    God bless them indeed.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    The truth hurts sometimes, TommyBoy, but the truth is what I gave you and nothing but the truth.
    Better you know now that you have been heading down the wrong path, and that this is going to take some time and effort for you to implement.
    On the plus side, if you take the time to explain WHAT you are trying to do, and you are willing to listen to people on this forum, then we can give you some good advice on designing and coding your process. Its OK to be a noob. We don't mind noobs on the forum. We do have little patience with people who want free advice and then insist on doing things wrong. That is just a waste of our time.
    So post a new thread desribing your situation and ask for some help on engineering the process.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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