May 9, 2002 at 7:38 am
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_co VarChar(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
Set auto_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
May 9, 2002 at 9:50 am
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
May 9, 2002 at 9:58 am
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.
May 9, 2002 at 9:58 am
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.
May 9, 2002 at 10:16 am
I`ll give the COALESCE a pop then.
Cheers,
Andy.
May 9, 2002 at 10:19 am
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