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 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy