title: How to return data from null INT value using LIKE clause in stored procedure SQL
body: <p>I have a stored procedure as follows:</p>
  
  <pre><code>@AgencyID VARCHAR(50),
  @TNID INT = NULL,
  @ProviderName VARCHAR(100) = NULL,
  @County VARCHAR(50) = NULL,
  @Region VARCHAR(50) = NULL
  
  SELECT * 
  FROM CustomerInfo
  WHERE AgencyID = @AgencyID 
    AND (TNID LIKE '%' + ISNULL(CAST(@TNID AS VARCHAR(50)), '') + '%') 
    AND (ProviderName LIKE '%' + ISNULL(@ProviderName, '') + '%') 
    AND (County LIKE '%' + ISNULL(@County, '') + '%') 
    AND (Region LIKE '%' + ISNULL(@Region, '') + '%')
  </code></pre>
  
  <p>The problem I am facing is whenever the integer TNID value is null my query doesn't return anything even thought some of the parameters have values. It doesn't accept null value to return accurate data although i have ISNULL function.</p>
  
  <p>If I use straight C# code, it works perfectly, but I would like to use the same scenario in my stored procedure to prevent SQL injection in the future</p>
  
  <pre><code>string query = "SELECT * FROM CustomerInfo WHERE AgencyID = '" + agency.Text + "' AND TNID LIKE '%" + tnid.Text + "%' AND ProviderName LIKE '%" + prvName.Text + "%' AND County LIKE '%" + countyList.Text + "%' AND Region LIKE '%" + region.Text + "%'";
  </code></pre>