title: SQL using case or if in function
body: <p>I have table of persons with first and middle names. I'm trying to write a function that check if in given names contains certain letters. For example 'oistmiahf' will return 'Smith'.</p>
  
  <p>I am trying to make it with case:</p>
  
  <pre><code>CREATE FUNCTION dbo.ufn_FindNames(@lettersToSearch nvarchar(50))
      RETURNS nvarchar(max)
  AS
  BEGIN
      DECLARE @firstName nvarchar(50) = (SELECT FirstName FROM Employees),
      @middleName nvarchar(50) = (SELECT MiddleName FROM Employees),
      @ifNoResult nvarchar(50) = 'No result'
  
      RETURN CASE
          WHEN (@firstName LIKE '%' + @lettersToSearch + '%') THEN @firstName + ' '
          WHEN (@middleName LIKE '%' + @lettersToSearch + '%') THEN @middleName + ' '
          ELSE @ifNoResult
      END
  END
  GO    
  </code></pre>
  
  <p>After this when I call function with " <em>EXEC dbo.ufn_FindNames'oistmiahf'</em> " I get error 
  <strong>" Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, &lt;, &lt;= , >, >= or when the subquery is used as an expression. "</strong></p>
  
  <p>If I try to make the same function, but with IF contition I have again same error.</p>
  
  <pre><code>CREATE FUNCTION dbo.ufn_FindNames(@lettersToSearch nvarchar(50))
      RETURNS nvarchar(50)
  AS
  BEGIN
      DECLARE @firstName nvarchar(50) = (SELECT FirstName FROM Employees), 
      @middleName nvarchar(50) = (SELECT MiddleName FROM Employees),
      @result nvarchar(50)
  
      IF(@firstName LIKE '%' + @lettersToSearch + '%')
      BEGIN
          RETURN @firstName + ' '
      END
  
  
      IF(@middleName LIKE '%' + @lettersToSearch + '%')
      BEGIN
          RETURN @middleName + ' '
      END
  
      RETURN @result
  END
  GO
  </code></pre>