Inline Conditional Statement in Stored Procedure

Question

Here is the pseudo-code for my inline query in my code:

select columnOne
from myTable
where columnOne = '#variableOne#'
  if len(variableTwo) gt 0
      and columnTwo = '#variableTwo#'
  end

I would like to move this into a stored procedure but am having trouble building the query correctly. I assume it would be something like

select columnOne
from myTable
where columnOne = @variableOne
  CASE
    WHEN len(@variableTwo) <> 0 THEN and columnTwo = @variableTwo
  END

This is giving me a syntax error.

Could someone tell me what I've got wrong.

Also, I would like to keep it to only one query and not just have one if statement. Also, I do not want to build the sql in the stored procedure and run Exec() on it.

Answer 1

Switch up your logic and you can get the result you want.

select columnOne
from myTable
where columnOne = @variableOne
and (len(@variableTwo) = 0 or columnTwo = @variableTwo)