title: Where do my parameter variables go in T-SQL function?
body: <p>This is the function that I am trying to make below with two parameters and one single output that is the matching words. I am using @searchentry and @bestmatch for my parameters. My questions is where should the parameters go in the function so that I can just call the function when it is created <code>Select dbo.FunMatch('enamel cleaner', 'cleaner')</code> it will excecute the function and return the matching words from the two arguments which would be 1 ?</p>
  <pre><code>Create Function dbo.FunMatch(
      @searchentry varchar,
      @bestmatch varchar
      ) 
      Returns INT
      As
      Begin
      Declare @output INT
      Set @output = (select
      @searchentry,
      @bestmatch,
      cast(count(isMatch) as float) as matchingWords
  from
  (
      select 
          s.value as word_from_search_entry_txt, 
          b.value as word_from_best_match,
          case 
              when s.value = b.value or s.value+'s'=b.value or s.value=b.value+'s' then 'match' 
              else null 
          end as isMatch,
          t.*
       from (
          SELECT 
              @searchentry,@bestmatch
          FROM #tmp_parts
          
      ) t
      cross apply 
      string_split(@searchentry, ' ') s
      cross apply
      string_split(@bestmatch, ' ') b
  ) a
  group by
      @searchentry,
      @bestmatch)
          
          Return @output
  </code></pre>
  <p>I am writing a function to return the matching words between two strings. <strong>example</strong> data below</p>
  <pre><code>CREATE TABLE #tmp_parts
  (
  search_entry_txt VARCHAR(30),
  best_match VARCHAR(30),
  );
  INSERT INTO #tmp_parts
  VALUES ('rotating conveyors', 'conveyor'),
  ('rivet tool', 'rivet nut tool'),
  ('enamel cleaner', 'cleaner'),
  ('farm ring', 'ring'),
  ('tire gauge', 'gauge'),
  ('ice cream','ice cream');
  </code></pre>
  <p>You can see the expected out here which is the matchingWords column</p>
  <pre><code>select
      search_entry_txt,
      best_match,
      cast(count(isMatch) as float) as matchingWords
  from
  (
      select 
          s.value as word_from_search_entry_txt, 
          b.value as word_from_best_match,
          case 
              when s.value = b.value or s.value+'s'=b.value or s.value=b.value+'s' then 'match' 
              else null 
          end as isMatch,
          t.*
       from (
          SELECT 
              search_entry_txt,best_match
          FROM #tmp_parts
      ) t
      cross apply 
      string_split(search_entry_txt, ' ') s
      cross apply
      string_split(best_match, ' ') b
  ) a
  group by
      search_entry_txt,
      best_match
  </code></pre>