SQL Tabled Valued functions

%3CLINGO-SUB%20id%3D%22lingo-sub-3333293%22%20slang%3D%22en-US%22%3ESQL%20Tabled%20Valued%20functions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3333293%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20there%2C%20I%20am%20learning%20T-SQL%20Table%20valued%20functions%20and%20would%20need%20some%20help.%3C%2FP%3E%0A%3CP%3EThe%20body%20of%20the%20table%20valued%20functions%20start%20with%20the%20return%20and%20then%20the%20select%20statement%20.%3C%2FP%3E%0A%3CP%3EEg%3A%20-%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-sql%22%3E%3CCODE%3ECREATE%20FUNCTION%20udfProductInYear%20(%40model_year%20INT)%0ARETURNS%20TABLE%20AS%0ARETURN%0ASELECT%20product_name%2Cmodel_year%2Clist_price%20FROM%0Aproduction.products%20WHERE%20model_year%20%3D%20%40model_year%3B%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EBut%20if%20I%20want%20to%20declare%20a%20variable%20and%20execute%20some%20commands%20and%20later%20select%20records%20from%20table%20then%20can%20I%20be%20able%20to%20do%20it%3F%20something%20like%20below%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-sql%22%3E%3CCODE%3ECREATE%20FUNCTION%20udfProductInYear%20(%40model_year%20INT)%0ARETURNS%20TABLE%20AS%0ABegin%20%0Adeclare%20%40year%3D%40model_year%2B2%20--increasing%20the%20year%20by%202%0ARETURN%0ASELECT%20product_name%2Cmodel_year%2Clist_price%20FROM%0Aproduction.products%20WHERE%20model_year%20%3D%20%40year%3B%0Aend%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EThank%20you%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Microsoft

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 

 

 

0 Replies