title: Selecting from where column equals a value or is null with cases (Mssql)
body: <p>In the query below im doing a search on a table called welds, this table has a column called Welds.Prefix which has NULL rows, rows with actual values, and rows with an empty value. I need help modifying my query below so that when the user inputs a non null and non empty value in @Prefix it will find the rows that match. And when @Prefix is blank it will find where Welds.Prefix is blank or where Welds.Prefix is null but im not sure how to go about it, I understand that I could do an update query and change the columns with null values to blank spaces but im looking for another way. I run the query below and I get an error "Incorrect syntax near the keyword 'OR'" I understand why im getting the error but im not sure how to fix it, thanks.</p>
  
  <p>Note: The @Prefix will be inputted on the front end and will not be bound to just '', I just put it in the query for questions sake.</p>
  
  <pre><code>@Prefix varchar(30) = ''
  
  select * 
  from welds
  WHERE Welds.Prefix = CASE WHEN @Prefix &lt;&gt; '' THEN @Prefix END
            OR Welds.Prefix = CASE WHEN @Prefix = '' THEN '' OR IS NULL END
  </code></pre>
  
  <p>EDIT:
  I basically want to selct rows where Weld.Prefix is null and where Weld.Prefix = ' ' when @Prefix = ' '</p>