Binary Column always returns 0 for ISNULL

Why don't i ever get return value of 1 when the following binary column (profSignature) is null?

RETURN SELECT ISNULL

(profSignature, profSignature)FROMmpProfilesWHEREApplicantID =CAST(@.CIDAS INT)ANDProfileID =CAST(@.PIDAS INT)

There is no conversion between GUID and INT that is what I think you are trying to do, try the link below for SQL Server data type conversion chart. Hope this helps.

http://msdn2.microsoft.com/en-us/library/ms187928.aspx

|||I forgot to add that the column type is "Image" in SQL 2005|||

Dup post

|||How would I get it so a <NULL> column value makes the stored procedure return 1 or 0?|||

I have checked the conversion chart again that is not valid either so check the create table statement below from Microsoft AdventureWorks it pick the columns relevant to your situation and you can get the information you are looking for. Sorry about the duplicate post. Hope this helps.

CREATE TABLE [EmployeePhoto] (
[EmployeePhotoID] [int] IDENTITY (1, 1) NOT NULL ,
[ThumbNailPhoto] [image] NULL ,
[ThumbnailPhotoFileName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LargePhoto] [image] NULL ,
[LargePhotoFileName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ModifiedDate] [datetime] NOT NULL CONSTRAINT [DF_EmployeePhoto_ModifiedDate] DEFAULT (getdate()),
CONSTRAINT [PK_EmployeePhoto_EmployeePhotoID] PRIMARY KEY CLUSTERED
(
[EmployeePhotoID]
) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

|||

If you need 1 on null try this:

RETURN SELECT ISNULL(profSignature,1)
FROMmpProfiles
WHEREApplicantID =CAST(@.CIDAS INT)
ANDProfileID =CAST(@.PIDAS INT)

or

RETURN (case
when exist(Select * FROMmpProfiles
WHEREApplicantID =CAST(@.CIDAS INT)
ANDProfileID =CAST(@.PIDAS INT)
and profSignature is null) then 1 -- for null

when exist(Select * FROMmpProfiles
WHEREApplicantID =CAST(@.CIDAS INT)
ANDProfileID =CAST(@.PIDAS INT)
and not profSignature is null) then 2 -- for not nulls

else

0 -- does not exists in database

end)

|||Thanks ... the case statement did the trick.sql

Best way to check if a field Is NULL

I usually check is a field is Null by doing something like this:

WHERE IsNull(myField,'') = ''

However, I just noticed that this is also return records where myField = 0

Does anyone know why?

What is the best and most common way to check if a field is null?

The best way to check if a field is null is

WHERE myField IS NULL

Are you by chance checking for a "blank" field instead of a null field?

|||

Zero is not null.

Try this:

Code Snippet

select *

from MyTable

where MyField IS NULL

|||Arnie,

The fact that rows with MyField = 0 are returned is not a problem with the query. If the type of MyField is integer and its value is zero, the query will test 0 = '', which is true. This is because '' is implicitly converted to integer in the comparison, and in SQL Server, cast('' as integer) has the value zero.

Steve Kass
Drew University
http://www.stevekass.com

|||

Steve,

...such rows would indicate that there is a problem in the query.

Thanks for the clarification. I was referring to the nature of the query -not the evaluation of implicit zero. The OP wanted NULL rows, so the way the query was written (invoking implicit conversions) would not satisfy his/her needs.

I could have more precise, but I dropped it. I'm glad you jumped in to dispel any misconceptions I may have created.