SQL Server Table valued functions

Question

Hi there, I am learning T-SQL Table valued functions and would need some help.

The body of the table valued functions start with the return and then the select statement .

Eg: - CREATE FUNCTION udfProductInYear (@model_year INT) RETURNS TABLE AS RETURN SELECT product_name,model_year,list_price FROM production.products WHERE model_year = @model_year

But if I want to declare a variable and execute some commands and later select records from table then can I be able to do it? something like below

CREATE FUNCTION udfProductInYear (@model_year INT)
RETURNS TABLE AS
Begin 
declare @year=@model_year+2 --increasing the year by 2
RETURN
SELECT product_name,model_year,list_price FROM
production.products WHERE model_year = @year;
end

Thank you

Answer 1

In this case it would not be inline table-valued function, it would be multi-statement table-valued function and its syntax will be

create function ufn_Something (@默 _year int)
return @Result table (....) -- definition of the table variable here
as
begin
....
....
insert into @Result (...)
select ....

return;
end