title: Assistance with SQL Server SELECT query
body: <p>I've just started to learn SQL and would be very thankful if you can help with SELECT query. There are input variables: <code>@p_IPN</code> and <code>@p_SecondName</code>, the table has <code>SECONDNAME</code>, <code>FIRSTNAME</code>, <code>MIDDLENAME</code> and <code>IPN</code> columns. </p>
  
  <p>I need to perform search in my table by the specified input characters with the following conditions:</p>
  
  <ul>
  <li><p>if none of variables is specified, the query should return the entire table;</p></li>
  <li><p>if <code>@p_IPN</code> OR <code>@p_SecondName</code> is specified, the query should perform search by <code>IPN LIKE @p_IPN OR SECONDNAME LIKE @p_SecondName</code>;</p></li>
  <li><p>if both variables are specified, the query should return rows with input characters of <code>@p_IPN</code> AND <code>@p_SecondName</code> (<code>IPN LIKE @p_IPN AND SECONDNAME LIKE @p_SecondName</code>)</p></li>
  </ul>
  
  <p>I used the following query: </p>
  
  <pre><code>IF (@p_IPN IS NULL AND @p_SecondName IS NULL)
      SELECT 
          CAST(IPN AS NVARCHAR(MAX)) AS IPN, FIRSTNAME, SECONDNAME, MIDDLENAME 
      FROM
          dbo.BE_BlackList
  ELSE IF (@p_IPN IS NULL)
      SELECT 
          CAST(IPN AS NVARCHAR(MAX)) AS IPN, FIRSTNAME, SECONDNAME, MIDDLENAME    
      FROM
          dbo.BE_BlackList
      WHERE 
          SECONDNAME LIKE @p_SecondName
  ELSE IF (@p_SecondName IS NULL)
      SELECT 
          CAST(IPN AS NVARCHAR(MAX)) AS IPN, FIRSTNAME, SECONDNAME, MIDDLENAME    
      FROM
          dbo.BE_BlackList
      WHERE 
          IPN LIKE @p_IPN
  ELSE
      SELECT 
          CAST(IPN AS NVARCHAR(MAX)) AS IPN, FIRSTNAME, SECONDNAME, MIDDLENAME    
      FROM
          dbo.BE_BlackList
      WHERE 
          IPN LIKE @p_IPN AND SECONDNAME LIKE @p_SecondName 
  </code></pre>
  
  <p>It works well, still, I need to have the same result using SELECT query.</p>
  
  <p>I tried this: </p>
  
  <pre><code>    SELECT CASE WHEN @p_IPN IS NULL AND @p_Secondname IS NULL THEN
              (SELECT cast (IPN AS nvarchar(max)) AS IPN, FIRSTNAME, MIDDLENAME, SECONDNAME FROM dbo.BE_BlackList)
  
                 WHEN @p_SecondName IS NULL THEN
              (SELECT cast(IPN AS nvarchar(max)) AS IPN, FIRSTNAME, MIDDLENAME, SECONDNAME FROM dbo.BE_BlackList
               WHERE IPN LIKE @p_IPN)
  
                 WHEN @p_IPN IS NULL THEN
              (SELECT cast(IPN AS nvarchar(max)) AS IPN, FIRSTNAME, MIDDLENAME, SECONDNAME FROM dbo.BE_BlackList
               WHERE SECONDNAME LIKE @p_Secondname)
  
           ELSE 
              (SELECT cast (IPN AS nvarchar(max)) AS IPN, FIRSTNAME, MIDDLENAME, SECONDNAME FROM dbo.BE_BlackList
               WHERE SECONDNAME LIKE @p_Secondname AND IPN LIKE @p_IPN)
           END
  FROM dbo.BE_BlackList
  </code></pre>
  
  <p>I get this error:</p>
  
  <blockquote>
    <p>Msg 116, Level 16, State 1, Line 10<br>
    Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.</p>
  </blockquote>
  
  <p>I am not sure how to use <code>CASE WHEN EXISTS</code> here (if possible at all). Could you help, please? </p>