Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2011
    Posts
    17

    Unanswered: Update Store Procedure

    Hi

    The current update store procedure that I have updates a list of input provided, but it there are fields that are left blank, they are being updated as null in the database.
    I'm having a trouble creating a store procedure that will just update the provided fields only.

    Please Help
    Thanks

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    If the procedure doesn't work what you want, it is probably because you wrote it incorrectly. As you didn't provide code, tables, data, etc., it is difficult to guess what might be wrong. A blind guess: WHERE clause is a culprit.

  3. #3
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by stahorse1 View Post
    I'm having a trouble creating a store procedure that will just update the provided fields only.
    Have you considered using proper WHERE clause to UPDATE only rows with "the provided fields only"?

  4. #4
    Join Date
    Nov 2011
    Posts
    17
    I have lot of fields, that might be updated on the run time. Now I'm struggling with a query for that, run time update.

    My current update would look like this:

    create or replace PROCEDURE SP_UPD_EXCEPTION_LOG
    (
    p_ExceptionID IN INTEGER,
    p_DateTimeStamp IN DATE,
    p_Code IN VARCHAR2,
    p_Description IN VARCHAR2,
    p_Information IN VARCHAR2,
    p_ApplicationID IN INTEGER,
    p_rowID OUT NUMBER
    )

    AS

    BEGIN
    UPDATE "ExceptionsLog"
    SET
    "DateTimeStamp" = SYSDATE,
    "Code" = p_Code,
    "Description" = p_Description,
    "Information" = p_Information,
    "ApplicationID" = p_ApplicationID

    WHERE "ExceptionID" = p_ExceptionID;

    END SP_UPD_EXCEPTION_LOG;


    but now if a user might update only two fields from that code, others are updated as null.

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >but now if a user might update only two fields from that code, others are updated as null.

    so you need to modify the procedure to do as you desire.
    try using IF ... THEN
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  6. #6
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Code:
    create or replace PROCEDURE SP_UPD_EXCEPTION_LOG
    (
      p_ExceptionID IN INTEGER,
      p_DateTimeStamp IN DATE,
      p_Code IN VARCHAR2,
      p_Description IN VARCHAR2,
      p_Information IN VARCHAR2,
      p_ApplicationID IN INTEGER,
      p_rowID OUT NUMBER
    )
    
    AS
    
    BEGIN
      UPDATE "ExceptionsLog"
      SET    
        "DateTimeStamp" = SYSDATE,
        "Code" = nvl(p_Code,"Code"),
        "Description" = nvl(p_Description,"Description"),
        "Information" = nvl(p_Information,"Information"),
        "ApplicationID" = nvl(p_ApplicationID,"ApplicationID")
    
      WHERE "ExceptionID" = p_ExceptionID;
      
    END SP_UPD_EXCEPTION_LOG;
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

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