problem with error handler
-
sorry again to bother you guys but this code seems so simple but it is not working.
I have to perform two validations.
The first one checks to see if a record already exists in the db. If it does, raise an error that says: record already exists.
This may not be the most elegant way to code it but works because I have tested it.
There is another validation that says:
check before inserting to ensure that no values or empty values are being inserted.
This has been a headached.
I created the field in the db as not null but we need to give the user a human readable error message.
can someone please tell me what is wrong?
thanks in advance
create procedure mainInput(
@accidentNumber int,
@orgcode int,
@empid int,
@dob datetime,
@accidentDate datetime,
@accidentTime datetime
)
as
declare @counter int
if @OrgCode = 0
or @EmpID = 0
or @dob = 0
begin
raiserror('orgcode and empid and dob
must be entered',13,1)
end
SELECT @Counter = Count(*)
FROM tblMain
WHERE @accidentNumber = trackingNumber
IF @Counter > 0
BEGIN
RAISERROR('Record already exists for this client',16,1)
ROLLBACK TRANSACTION
END
ELSE
BEGIN
/* Create and populate new tblAccidentEvent Record */
INSERT INTO tblMain(
trackingNumber,
OrgCode ,
EmPID,
AccidentDate,
AccidentTime
)
VALUES(
@accidentNumber,
@OrgCode,
@Empid,
@AccidentDate,
@AccidentTIme
)
END
I have tried :
if @orgoce is null etc but it still will not catch the error when no value is entered for orgcode and the rest of the codes that are being checked for nulls.
-
This is probably too simple... Remember that checking for 0 and checking for NULL are not the same thing. If the parameter is passed as NULL it will bypass the check.
Try if ISNULL( @OrgCode, 0 ) = 0
or ISNULL( @EmpID, 0 ) = 0
or ISNULL( @dob, 0 ) = 0
Guarddata-
-
As an extension to guarddata's response, you may choose to block the invalid inserts at the table definition, with somethin like the following. The error message generated is readable to the majority of humans.
create table tblMain (
accidentNumber int null,
orgcode int null,
empid int null,
dob datetime null,
accidentDate datetime null,
accidentTime datetime null,
Constraint [**** A value must be supplied for all fields ****]
check (
isnull(accidentNumber, 0) <> 0
and isnull(orgcode, 0) <> 0
and isnull(empid, 0) <> 0
and isnull(dob, '00:00') <> '00:00'
and isnull(accidentDate, '00:00') <> '00:00'
and isnull(accidentTime, '00:00') <> '00:00'
)
)Cheers,
- Mark
Cheers,
- Mark -
thanks guarddata and mccork!
I tried both responses and like mine, I keep getting the below message when I try testing.
Procedure 'mainInput' expects parameter '@orgcode', which was not supplied.
I was looking to get either my error message or this from mccork:
[**** A value must be supplied for all fields ****
-
This message is not a failure within the procedure but during the call to it. An entry for @orgCode must be supplied unless you put a default value in the procedure parameter list.
create procedure mainInput(
@accidentNumber int = 0,
@orgcode int = 0,
@empid int = 0,
@dob datetime = getdate(),
@accidentDate datetime = getdate(),
@accidentTime datetime = getdate()
would probably avoid the error, but it may not be what you want. Processes calling the procedure need to either supply a NULL or a value for your parameter.
Guarddata-
-
sorry, guarddata and mccork.
Last time I was herem, I was working from home.
As soon as I discovered that I had been testing this sp incorrectly all along, I came back to tell you guys but I had a hard time logging into my isp.
In any case, your code is throwing the exception but still would allow a null value to be inserted into the table.
I am using mccork's because it doesn't allow nulls, however, I learned something new today from you, thank you.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply