title: SQL Table Join with Wildcard Variables
body: <p>I have two joined tables. Table 1 has client data and table 2 has their status.</p>
  
  <p>I need to have a wildcard name search but I only want it to return results based on a status from table 2. This works when I actually enter some data in the @ClientName variable but when it is left empty it returns all records and ignores the @ClientStatus variable. Any ideas on how to fix this?</p>
  
  <pre><code>DECLARE @ClientName NVARCHAR (100) = '' 
  DECLARE @ClientStatus INT = 1 
  
  SELECT DBO.CLIENTS.CLIENTID, 
         DBO.CLIENTS.FIRST_NAME, 
         DBO.CLIENTS.LAST_NAME, 
         DBO.CLIENTS.PHONE_NUMBER, 
         DBO.CLIENTS.MOBILE_NUMBER, 
         DBO.CLIENTS.HOUSE_ADDRESS_NUMBER, 
         DBO.CLIENTS.STREET_NAME, 
         DBO.CLIENTS.TOWN_CITY, 
         DBO.CLIENTS.POST_CODE, 
         DBO.CLIENTS.EMAIL_ADDRESS, 
         DBO.CLIENTS.CLIENT_SOURCE, 
         DBO.CLIENTS.CLIENT_STATUS, 
         DBO.CLIENTS.DATE_ADDED, 
         DBO.CLIENTS.CALL_DATE, 
         DBO.CLIENTS.CALL_TIME, 
         DBO.CLIENT_STATUS.CLINIC_STATUS 
  FROM   DBO.CLIENTS 
         INNER JOIN DBO.CLIENT_STATUS 
                 ON DBO.CLIENTS.CLIENT_STATUS = DBO.CLIENT_STATUS.STATUSID 
  WHERE  ( DBO.CLIENT_STATUS.CLINIC_STATUS = @ClientStatus ) 
         AND ( DBO.CLIENTS.LAST_NAME LIKE @ClientName + '%' ) 
          OR ( DBO.CLIENTS.MOBILE_NUMBER LIKE @ClientName + '%' ) 
  </code></pre>