SQL Server: INNER JOIN with WHERE clause not working

Question

Expecting an output from the following query;

ALTER PROCEDURE [dbo].[ASASearchMed]
(
    @Endorsed Varchar(200),
    @Authorized Varchar(200),
    @Notes Varchar(200),
    @LastName Varchar(50),
    @FirstName Varchar(50),
    @MiddleName Varchar(50)
)
AS
  SELECT 
     ASA.ASAID
     , ASA.ProID
     , PRO.LastName
     , PRO.FirstName
     , PRO.MiddleName
     , ASA.ASAType
     , ASA.Endorsed
     , ASA.Authorized
     , ASA.Notes
     , ASA.DateReg
     , ASA.DateMod
     , ASA.UserReg
     , ASA.UserMod
  FROM
     dbo.ASA
  INNER JOIN 
     dbo.PRO ON ASA.ProID = PRO.ProID
  WHERE
     ASA.ASAType = 'Medical'
     AND NULLIF(ASA.MedGLCtrlNo, '') IS NULL OR ASA.MedGLCtrlNo = '' 
     AND (ASA.Endorsed LIKE '%' + @Endorsed + '%' OR
          ASA.Authorized LIKE '%' + @Authorized + '%' OR
          ASA.Notes LIKE '%' + @Notes + '%' OR
          PRO.LastName LIKE '%' + @LastName + '%' OR
          PRO.FirstName LIKE '%' + @FirstName + '%' OR
          PRO.MiddleName LIKE '%' + @MiddleName + '%')

When I try to run this, I got nothing as it looks like it didn't even execute but when I tried the query in Management Studio the problem persist.

I'm trying to filter the search with those records only tagged as Medical and is NULL or with '' empty string and should also match the data given on each fields below.

My code for search

 Public Sub Search()
        Dim StrSQL As String = "ASASearchMed"
        cLib = New clsLibrary
        cLib.openConnection(strConnection)
        If Not cLib.isConnectionOpen() Then
            Exit Sub
        End If
        cLib.initializeCommand(StrSQL)
        cLib.addParameter("@Endorsed", SqlDbType.VarChar, txtSearch.TextLength, txtSearch.Text)
        cLib.addParameter("@Authorized", SqlDbType.VarChar, txtSearch.TextLength, txtSearch.Text)
        cLib.addParameter("@Notes", SqlDbType.VarChar, txtSearch.TextLength, txtSearch.Text)
        cLib.addParameter("@LastName", SqlDbType.VarChar, txtSearch.TextLength, txtSearch.Text)
        cLib.addParameter("@FirstName", SqlDbType.VarChar, txtSearch.TextLength, txtSearch.Text)
        cLib.addParameter("@MiddleName", SqlDbType.VarChar, txtSearch.TextLength, txtSearch.Text)
        objCommand.ExecuteNonQuery()
        lview.Items.Clear()
        DisplayData()
    End Sub

and how I display the output on listview

    Public Sub DisplayData()
        Dim objDataReader = objCommand.ExecuteReader
        While objDataReader.Read
            Dim listview As New ListViewItem
            With listview
                .Text = objDataReader.Item("ASAID")
                .SubItems.Add(objDataReader("ProID"))
                .SubItems.Add(objDataReader("LastName"))
                .SubItems.Add(objDataReader("FirstName"))
                .SubItems.Add(objDataReader("MiddleName"))
                .SubItems.Add(objDataReader("ASAType"))
                .SubItems.Add(objDataReader("Endorsed"))
                .SubItems.Add(objDataReader("Authorized"))
                .SubItems.Add(objDataReader("Notes"))
                .SubItems.Add(objDataReader("DateReg"))
                .SubItems.Add(objDataReader("DateMod"))
                .SubItems.Add(objDataReader("UserReg"))
                .SubItems.Add(objDataReader("UserMod"))
            End With
            lview.Items.Add(listview)
        End While
    End Sub

.

    Public Sub LoadData()
        Dim StrSQL As String = "ASAViewMed"
        cLib = New clsLibrary
        cLib.openConnection(strConnection)
        If Not cLib.isConnectionOpen() Then
            Exit Sub
        End If
        cLib.initializeCommand(StrSQL)
        objCommand.ExecuteNonQuery()
        lview.Items.Clear()
        DisplayData()
    End Sub

Hope someone could help or enlighten me. Thanks in advance!

Answer 1

Try

   AND (ASA.MedGLCtrlNo IS NULL OR ASA.MedGLCtrlNo = '' )

check the ( ) grouping too on this example and in your WHERE clause overall.