March 5, 2007 at 7:36 am
I have a Proc that has a few parameters passed to it. I check for the required values when the SP is run to make sure that they are being passed before continuing processing. I check for correct value in a range or if a null is being passed etc... I was doing some testing on a Proc and couldn't find out why when I passed a blank value to it the Error wasn't being triggered.
I found that if you set:
isnull(@Param,0)= 0 you can check for Blank values or Null values
but if you set:
isnull(@Param,1)=1 this won't work for blank values.
Here is my simplified code:
CREATE PROCEDURE s_Test
@ID int
as
IF isnull(@ID,0)= 0
BEGIN
PRINT 'ID was not supplied or is null'
RETURN
END
exec dbo.s_Test @ID=''
ID was not supplied or is null
Now change the isnull value:
IF isnull(@ID,1)= 1
BEGIN
PRINT 'ID was not supplied or is null'
RETURN
END
exec dbo.s_Test @ID=''
The sp returns nothing. It seems a blank value is treated as a null only when you set a zero to it. Anything larger like a 1 or a 2 does not treat the blank value as a null.
March 5, 2007 at 7:45 am
First of all, the zero-length string ('') is not NULL. SQL is implicitly casting the zero-length string to 0.
Secondly, you shouldn't be passing a zero-length string to an INT parameter. If you want to pass a NULL, pass a NULL. Consider this:
CREATE PROCEDURE s_Test
@ID int = NULL
as
IF @ID IS NULL
BEGIN
PRINT 'ID was not supplied or is null'
RETURN
END
GO
EXEC dbo.s_Test @ID=NULL
EXEC dbo.s_Test
March 5, 2007 at 8:30 am
Mike,
Thanks for the reply.
I knew that a blank wasn't a null but I never knew until today that SQL casts a blank value as a zero. I validate the input from web pages as well as applications. I want to validate against all possibilities so I like to include blank or null values. This is really used for data validation before I continue running the proc. so I have to check for any value that shouldn't be allowed whether blank or null regardless of datatype. Thanks again for the response.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply