title: Declare a table variable within a user defined function
body: <p>I am working on a sample query and this is the only <code>outcomes</code> table I got:</p>
  
  <pre><code>    **ship**
  Bismarck
  California
  California
  Duke of York
  Fuso
  Hood
  King George V
  Kirishima
  Prince of Wales
  Rodney
  Schamhorst
  South Dakota
  Tennessee
  Washington
  West Virginia
  Yamashiro
  </code></pre>
  
  <p>I am working on replace the characters between the first and the last spaces in the strings with a <code>*</code>. And I got the following code, which is correct:</p>
  
  <pre><code>select 
          left(ship, charindex(' ', ship) - 1) + ' ' + 
          replicate('*', charindex(' ', substring(ship, charindex(' ', ship) + 1, len(ship))) + 1 -2) + ' ' +
          reverse(left(reverse(ship), charindex(' ', reverse(ship)) - 1))
  from outcomes
  where charindex(' ', substring(ship, charindex(' ', ship) + 1, len(ship))) &gt; 1;
  </code></pre>
  
  <p>The code is working but I would like to make a table variable within a user defined function so that I can reuse it without much difficulty. The code I used to declare the table variable is as the following and is correct:</p>
  
  <pre><code>declare @ship_outcome table
  (   final_work nvarchar(30)
  )
  
  insert into @ship_outcome (final_work)
  select 
          left(ship, charindex(' ', ship) - 1) + ' ' + 
          replicate('*', charindex(' ', substring(ship, charindex(' ', ship) + 1, len(ship))) + 1 -2) + ' ' +
          reverse(left(reverse(ship), charindex(' ', reverse(ship)) - 1))
  from outcomes
  where charindex(' ', substring(ship, charindex(' ', ship) + 1, len(ship))) &gt; 1;
  
  select * from @ship_outcome
  </code></pre>
  
  <p>The problem is, when I used the following code to make it a user defined function:</p>
  
  <pre><code>CREATE FUNCTION dbo.shippad (@tbl nvarchar(30))
  RETURNS TABLE
  AS
  RETURN 
  
      declare @ship_outcome table
      (
          final_work nvarchar(30)
      )
  
      insert into @ship_outcome
  
      select 
          left(ship, charindex(' ', ship) - 1) + ' ' + 
          replicate('*', charindex(' ', substring(ship, charindex(' ', ship) + 1, len(ship))) + 1 -2) + ' ' +
          reverse(left(reverse(ship), charindex(' ', reverse(ship)) - 1))
      from outcomes
      where charindex(' ', substring(ship, charindex(' ', ship) + 1, len(ship))) &gt; 1
      select * from @ship_outcome
  ;
  </code></pre>
  
  <p>The system says that <code>Incorrect syntax near the keyword 'declare'.</code></p>
  
  <p>I can't figure out how i got this wrong. Please help.</p>