Anything wrong with this SP?

  • Can anyone tell me if there anything wrong with this SP as it causes a Exception Violation in the db?!

    CREATE PROCEDURE usp_auto_updaterec

    (

    @auto_id VarChar(36),

    @reg_no VarChar(10),

    @make VarChar(20),

    @model VarChar(30),

    @eng_size decimal,

    @lease_from datetime,

    @term int,

    @milage VarChar,

    @lease_until datetime,

    @payroll VarChar(15),

    @own_fore VarChar(20),

    @own_surn VarChar(30),

    @own_div VarChar(20),

    @own_loc VarChar(15),

    @hire_coVarChar(20),

    @mod_comments Varchar(50),

    @engine_type VarChar(1),

    @hands_free Bit,

    @archived Bit,

    @comments text,

    @mgt_grade VarChar (15),

    @gde_allowance Money,

    @needs_status VarChar (25),

    @mth_rentmaint Money,

    @xs_milage_rte Money,

    @fuel_card Bit,

    @cash_alt Money,

    @cash_alt_began datetime,

    @cash_alt_renew datetime,

    @cash_alt_cease datetime,

    @trade_down Money,

    @add_extras Money,

    @ni_no VarChar (9),

    @ret_date DateTime

    )

    AS

    BEGIN

    SET NOCOUNT OFF

    BEGIN TRANSACTION update_rec

    UPDATE auto_main

    Setauto_id = auto_id,

    reg_no = @reg_no,

    make = @make,

    model = @model,

    eng_size = @eng_size,

    lease_from = @lease_from,

    term = @term ,

    milage = @milage,

    lease_until = @lease_until,

    payroll = @payroll,

    own_fore = @own_fore,

    own_surn = @own_surn,

    own_div = @own_div,

    own_loc = @own_loc,

    hire_co = @hire_co,

    mod_comments = @mod_comments,

    engine_type = @engine_type,

    hands_free = @hands_free,

    archived = @archived,

    comments = @comments,

    mgt_grade = @mgt_grade,

    gde_allowance = @gde_allowance,

    needs_status = @needs_status,

    mth_rentmaint = @mth_rentmaint,

    xs_milage_rte = @xs_milage_rte ,

    fuel_card = @fuel_card,

    cash_alt = @cash_alt,

    cash_alt_began = @cash_alt_began,

    cash_alt_renew = @cash_alt_renew,

    cash_alt_cease = @cash_alt_cease,

    trade_down = @trade_down,

    add_extras = @add_extras,

    ni_no = @ni_no,

    ret_date = @ret_date

    WHERE reg_no = @auto_id

    IF(@@error = 0)

    BEGIN

    COMMIT TRANSACTION update_rec

    RETURN 0

    END

    ELSE

    BEGIN

    ROLLBACK TRANSACTION update_rec

    RETURN 1

    END

    END

    GO

  • Which version of sql are you using?

    Are you calling through QA or application when the violation occurs?

    Generally when I have experienced exception violations is when a procedure is expecting parameters and a null is sent to it.

    I generally find this by running profiler and looking specifically for what is being sent to the SP.

    Hope this helps.

    Tom Goltl

  • I agree, it's probably a NULL issue. You can prevent yourself from it using the following syntax:

    Set auto_id = auto_id,

    reg_no =coalesce(@reg_no,reg_no)

    make = coalesce(@make,make)

    etc.

    That way you will leave the value in the column if the passing value is NULL.

  • It happens from VB and QA.

    The weird thing is, is that the SP works, it updates the data as expected but then gets the exception violation??

    Andy.

  • I`ll give the COALESCE a pop then.

    Cheers,

    Andy.

  • I think the COALESCE worked guys! Thanks so much, that problems been bugging me for ages!!!

    Thanks.

    Andy.

Viewing 6 posts - 1 through 5 (of 5 total)

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