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