Need to get SQL query

try

Decalre @MRN varchar(50) --parameter
Decalre @FName varchar(50)--parameter
Decalre @LName varchar(50)--parameter
Decalre @DOB varchar(50)--parameter

SELECT  [Last_Name],[First_Name], [middle_name],[Patient_No],[gender],[Date_of_Birth] 
FROM [Demographics] 
where Patient_No= 
    Case
       When @MRN <>'' Then
         @MRN 
       Else
         Patient_No
   End
And First_Name like 
  Case 
     When @FName <>'' Then
        @FName+'%'
      Else 
       '%'
   End
And Last_Name like 
  Case 
     When @LName<>'' Then
        @LName+'%'
      Else 
       '%'
   End
And Date_of_Birth=
  Case 
     When @DOB <>'' Then
        @DOB
      Else 
       Date_of_Birth
   End

Ref.
https://msdn.microsoft.com/en-us/library/ms181765.aspx[^]

The main problem you have is testing for the Column = '' rather than the variable as Zoltan has pointed out, but you're also not catering for null values.

The following solution will cater for any of the columns being null AND/OR any of the variables being null

-- Variables
DECLARE @MRN VARCHAR(3)
DECLARE @FN varchar(30)
DECLARE @LN varchar(30)
DECLARE @DOB Date

-- Test data (changes)
SET @MRN = ''
SET @FN = NULL
SET @LN = 'Smith'
SET @DOB = NULL

SELECT  [Last_Name],[First_Name], [middle_name],[Patient_No],[gender],[Date_of_Birth]
FROM [Demographics]
where
ISNULL([Patient_No],'') LIKE COALESCE(@MRN + '%',Patient_No,'%')
AND
ISNULL([Last_Name],'') LIKE COALESCE(@LN + '%', Last_Name, '%')
AND
ISNULL([First_Name],'') LIKE COALESCE(@FN + '%', First_Name, '%')
AND
ISNULL(Date_of_Birth,'14-OCT-1066') = COALESCE(@DOB, Date_of_Birth, '14-OCT-1066')

A couple of points of interest:
- I used 14-OCT-1066 for handling null dates of birth, largely because no-one is likely to have that date of birth - you might need to change this depending on your context. (Aside - it's the date of the Battle of Hastings if you're interested)

- I used LIKE for the Patient_No instead of = to simplify the code - i.e. to avoid having to use a case statement. Otherwise that first condition would have been

ISNULL([Patient_No],'') = CASE WHEN @MRN='' THEN ISNULL(Patient_No,'') ELSE COALESCE(@MRN,Patient_No,'') END