Declare a table variable within a user defined function

Question

I am working on a sample query and this is the only outcomes table I got:

    **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

I am working on replace the characters between the first and the last spaces in the strings with a *. And I got the following code, which is correct:

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))) > 1;

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:

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))) > 1;

select * from @ship_outcome

The problem is, when I used the following code to make it a user defined function:

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))) > 1
    select * from @ship_outcome
;

The system says that Incorrect syntax near the keyword 'declare'.

I can't figure out how i got this wrong. Please help.

Declare a table variable within a user defined function

1 Answers