Create function failed: Msg 102, Level 15, State 1, Procedure mysal, Line 5

Question

create function mysal(@sal decimal(8,2))
returns @table table
(
    @eid int,
    @firstname varchar(20),
    @salary decimal(8,2),
    @doj date
)
as
begin
    insert @table 
    select * from employees where salary>@sal;
return
end

Error: Msg 102, Level 15, State 1, Procedure mysal, Line 5 Incorrect syntax near '@eid'.

Answer 1

The return table column names should not have @ in them, and the ; should be removed.

create function mysal(@sal decimal(8,2))
returns @table table
(
    eid int,
    firstname varchar(20),
    salary decimal(8,2),
    doj date
)
as
begin
    insert @table 
    select * from employees where salary > @sal

    return
end

Just a warning, this kind of multi-statement UDFs can be really bad for performance and using "Select *" causes your function to break if someone adds a new column to the table.