Post reply

Help in Updating an exception table

  • February 2, 2012 at 10:27 pm

    #250269

    Hi all,

    I have to write SP to update an exception table. The problem is while a new record comes in, the SP has to update three date fields sequentially. Below are the fields it has,

    1. RecordID

    2. Exception_Detail

    3. Exception_Timestamp -- Timestamp the exception occurred (This can be a getdate())

    4. Record_Changed_Timestamp --- Date this record was last changed

    5. Record_Created_Timestamp -- Date this record was first created

    The 3rd and 4th fields were used when the exception occurs again and again(basically update)

    Thanks in advance

  • February 3, 2012 at 4:39 am

    #1442310

    Short answer:

    In the SP, write the update statement for the columns.

    and after it put:

    if @@rowcount < 1

    insert statment

    @@rowcount contains the number of affected rows by the last statment,

    if the row is a new row no row are updated and its returns 0, so you insert a the new row

    if it already updated the row, by pass the insert.

    😎

  • February 6, 2012 at 2:53 am

    #1442962

    Hello all

    May i have an useful idea for you

    First you can declare a new date parameter then

    into an update statement ,You can assign it to third value if not null ,,And at every update you will reterive this parmeter and assign the date fied with it ,then you can increment on it as you like.

    Does it makes sense ?

  • February 6, 2012 at 3:18 am

    #1442969

    If I'm understanding you correctly (you want to either insert or update depending whether the exception is already there)

    Use Merge.

    Rough syntax:

    MERGE

    [ INTO ] <target_table> [ WITH ( <merge_hint> ) ] [ [ AS ] table_alias ]

    USING <table_source>

    ON <merge_search_condition>

    [ WHEN MATCHED [ AND <clause_search_condition> ]

    THEN <merge_matched> ] [ ...n ]

    [ WHEN NOT MATCHED [ BY TARGET ] [ AND <clause_search_condition> ]

    THEN <merge_not_matched> ]

    [ WHEN NOT MATCHED BY SOURCE [ AND <clause_search_condition> ]

    THEN <merge_matched> ] [ ...n ]

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • February 6, 2012 at 3:41 am

    #1442978

    All answers ill do the trick.

    The solution from myself is just a trick to put both insert and update statements in the same procedure.

    Jonhitech solutions is best for OO applications with layers or MVC (business logic lies in the app not in the BD).

    You ill pass the id of the object as a parameter, if the objects is not persisted yet the id ill be null or zero.

    Gail solutions is the more elegant (as usual) but works only in MSSQL Server 2008.

  • February 6, 2012 at 4:09 am

    #1442990

    jcb (2/6/2012)

    Gail solutions is the more elegant (as usual) but works only in MSSQL Server 2008.

    Which, since this is posted in the SQL 2008 forums is probably not a problem.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • February 6, 2012 at 4:54 am

    #1443008

    Gail,

    After you post I changed my avat accordly.

  • February 6, 2012 at 4:26 pm

    #1443496

    Thanks, all for your help.Gail ur solution has helped me a lot, Thanks much.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply