SQL Server In-line table function-CodePudding

I am having trouble creating an in-line table function (a function which returns a table).

The function is very simple - it takes 'first name'(first letter) 'last name' birth date:

CREATE FUNCTION dbo.fn_username
RETURNS TABLE   
AS
    RETURN 
        (SELECT
             LEFT(FirstName, 1)   LastName  
             CAST(YEAR(BirthDate) AS varchar(20)) 
         FROM Employees)

I get these errors:

Msg 102, Level 15, State 1, Procedure fn_username, Line 2 [Batch Start Line 26]
Incorrect syntax near 'RETURNS'.

Msg 178, Level 15, State 1, Procedure fn_username, Line 4 [Batch Start Line 26]
A RETURN statement with a return value cannot be used in this context.

Can anyone please tell me what is its problem? I already build similar functions and didn't have any problem .

Thank you all !

CodePudding user response：

There are a few problems.

You don't define your parameters. If your function has no parameters, you still must define that with () after the function's name.
You are over using parenthesis (()). You wrap the entire query in these, making it a subquery and implying you want to return a scalar value, not a table.
You omit an alias for your column.

Fix these, and you end up with the following:

CREATE FUNCTION dbo.fn_username()
RETURNS table   
AS RETURN
    SELECT CONCAT(LEFT(FirstName,1),LastName,YEAR(BirthDate)) AS YourColumnAlias
    FROM dbo.Employees;

Note I also switch to CONCAT as it's more concise, though if you want NULL if any of the columns are NULL you'll need to switch back to the concatenation operator ( ).