Tonmoy Saha Tonmoy Saha - 18 days ago 6
SQL Question

Getting null value in status field. Can anyone explain what i am going wrong

I want to status field based on case condition. but i am getting null. plz help.

Declare @Status NVarchar(20);
Set @Status = Case When Exists ( Select GunSerialNo
From dbo.ArmouryIssueGun
Where ModifiedOn != Null
And CreatedOn != Null ) Then 'In Field'
When Exists ( Select GunSerialNo
From dbo.ArmouryIssueGun
Where ModifiedOn = Null
And CreatedOn != Null ) Then 'In Armory'
End;
-- Insert statements for procedure here

Select (Select BranchName
From Branch
Where BranchId = gun.BranchId
) As BranchName
, gun.SerialNo As GunSerialNo
, gun.GunType
, gun.ModelNo
, gun.GunId
, Convert(Varchar(12), aig.CreatedOn, 103) IssueDate
, Substring(Convert(Varchar(20), aig.CreatedOn, 9), 13, 5) + ' ' + Substring(Convert(Varchar(30), aig.CreatedOn, 9), 25, 2) As IssueTime
, cl.LicenceHolderName As CarriedBy
, (Select TypeName
From dbo.CommonValues
Where ID = aig.Purpose
) As Purpose
, @Status As status
, Convert(Varchar(12), aig.ModifiedOn, 103) As CollectedDate
, Substring(Convert(Varchar(20), aig.ModifiedOn, 9), 13, 5) + ' ' + Substring(Convert(Varchar(30), aig.ModifiedOn, 9), 25, 2) As TimeIn
From dbo.CarryAndUseLicence cl
Join dbo.Branch b
On b.BranchId = cl.BranchId
Join dbo.Gun gun
On cl.GunSerialNo = gun.SerialNo
Join dbo.ArmouryIssueGun aig
On aig.StaffId = cl.StaffId;

Answer

Your problem is here:

Set @Status = Case When Exists ( Select GunSerialNo
                                 From   dbo.ArmouryIssueGun
                                 Where  ModifiedOn != Null
                                        And CreatedOn != Null ) Then 'In Field'
                   When Exists ( Select GunSerialNo
                                 From   dbo.ArmouryIssueGun
                                 Where  ModifiedOn = Null
                                        And CreatedOn != Null ) Then 'In Armory'
              End;

Specifically these parts:

 Where ModifiedOn != Null
 And CreatedOn != Null 

And

Where ModifiedOn = Null
And CreatedOn != Null

NULL cannot be equal to anything. Not even to another NULL. Thus, you cannot use = and != to compare them. You need to use IS NULL and IS NOT NULL instead.

Try changing them to the following:

 Where ModifiedOn Is Not Null
 And CreatedOn Is Not Null 

And

Where ModifiedOn Is Null
And CreatedOn Is Not Null
Comments