Checking for null values

[siena]

I have simplified the below code (for example).

I am trying to perform 2 validations.
A, I need to ensure that no duplicates are entered into the main table.
That seems to work well so far.
The other validation I am trying to make is to ensure that certain fields are not left blank(they must have values) for data to be successully inserted.
So far, that is not working.
Can someone please take a looka at this code and tell me what is wrong with it?
Thanks in advance.

SELECT @Counter = Count(*)
FROM tblMain
WHERE @ClientID = ClientID
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 tblAccidentEvent(
ClientID,
OrgCode ,
EmPID,
AccidentDate,
AccidentTime
)
VALUES(
@ClientID,
@OrgCode,
@Empid,
@ClientDate,
@ClientTIme
)
End
select @OrgCode = OrgCode, @EmpID = EmpID
from tblMain
where @ClientID = ClientID
If @OrgCode is null or @Empid is null
raiserror("Orgcode and empid must be selected", 16,1)
End

END

 

[essa2000]

Dear ;

I am little bit confused in this code b/c

1- you are inserting data into tblAccidentEvent table and checking null values into tblMain , right , so I am confused b/c you I think you are talking about one table and you checked duplicate into tblMain. So, Please , see into it as well.

2- Okay , now another thing , checking for null values should be before insert query , if these values are null so there is no need to execute this query for nothing. Rasieerror before running this query !

3- Third one , you can make it in table design , not allowed null values in those certain fields , so SQL Server will take care of it and raise error automatically !

Try these things and feed back to us !

Thanks.

Muhammad Essa Mughal
Software Engineer
essamughal@yahoo.com

 

[siena]

issue #1, you are correct, the tablename should be tblMain.
it was a typo but the name is correct in my actual code.
I will change the code. I was a bit confused about running both the already exists erorr checking and null value at the same place (exp. matters).

issue #3, I am using asp on the front end.
My table is designed to not allow null values and it is not allowing for null values.
However, I wanted a human readable error and I wanted to check that error on the backend.
Even after moving the error checking code to the top of the stored proc (where the already Exists code is).
It is still not working.

 

[essa2000]

Okay, before explaining it more , please can you check it with the following lines replacement

If @OrgCode is null or @Empid is null
raiserror("Orgcode and empid must be selected", 16,1)
End

Replace this with

If Len(ltrim(rtrim(@OrgCode))) = 0 or
Len(ltrim(rtrim(@Empid))) = 0
raiserror("Orgcode and empid must be selected", 16,1)
End

Please, check it , may be your variable are not null but with space and you are considering them as null , there is difference b/w null and space.

Get back to me !

Thanks.

Muhammad Essa Mughal
Software Engineer
essamughal@yahoo.com

 

[laado]

Hi Siena

I hope i understand your Problem properly...

Create Procedure AccidentEventPut(
@ClientID INT,
@OrgCode INT, **Don't Accept Null Values
@Empid INT **Don't Accept Null Values
)
AS
DECLARE @Success TINYINT,
@Failure TINYINT

SELECT @Success = 1,
@Failure = 0,

IF EXISTS (SELECT 1
FROM tblAccidentEvent
WHERE ClientID = @ClientID)
BEGIN
GOTO ERR_HANDLER
END
ELSE
BEGIN
/* Create and populate new tblAccidentEvent Record */
INSERT INTO tblAccidentEvent(
ClientID,
OrgCode ,
EmPID,
AccidentDate,
AccidentTime
)
VALUES(
@ClientID,
@OrgCode,
@Empid,
@ClientDate,
@ClientTIme
)
End
END

then Select what you want to return....

Return @Success


ERROR_HANDLER
RAISERROR('Record already exists for this Client',16,1)

RETURN @Failure

 

[siena]

hi laado and essa2000.
laado, the code I have that checks for duplicate entries works fine.
and I know that a field of int won't accept nulls but I want a message that can be understood by the client using this application.
I just want to see how the two error handling codes can co-exist.

 

[laado]

Siena
You can generate Two different Errors by Storing
then in different error string as

Declare @ErrString as varchar(255)

for a error
Select @ErrString = 'errors here...'
goto err_handler

err_handler
RAISERROR(@ErrString ,16,1)
.....

Just a suggestion
If you know you don't Parameters with Null Values
Why not handle in client side rather than connecting
to db and ....


Any way hope this helps

cheers
Daljinder

 

[BogdanMBM]

Hi, siena!
You say you allready designed your table not to allow nulls in "OrgCode" and "EmpID" columns.
B/c of this, I expect you to get the server native error message about those null values when you run the INSERT query. Your own check for nulls will not be performed by the server, b/c is after the query thet generates the error.
If you wanna catch the error before the server daz, try this BEFORE the INSERT query:

If @OrgCode is null or @Empid is null
raiserror("Orgcode and empid must be selected", 16,1)
End

You allready know those values so it should not be a problem.

Hope it helps!

Bogdan.