SQL SERVER Functions: in-depth analysis of table Valued Functions

In some cases, the following table value functions may be used. Table value functions are mainly used for data calculation and return result sets, which can contain parameters (a big difference with views ), if the function does not have much logical processing, such as variable definition and judgment,
The returned result set of the Table value function can be written as follows:
Copy codeThe Code is as follows: create function Fun_GetReportNews (@ type varchar (10 ))
RETURNS TABLE
AS
RETURN
(
SELECT TPR_ID, TPR_Title, TPR_Date FROM TP_ReportNews WHERE TPR_Type = @ type
)

Select xx from Fun_GetReprotNews ('xx') when calling ')
If the function needs to define variables and determine what to do with computing, the writing method is a little different. You need to define the table variables. The table value function does not allow creating temporary tables, only table variables are allowed.
A simple style is as follows:Copy codeThe Code is as follows: create function FUN_GetInfoList (@ type varchar (10 ))
RETURNS @ Table TABLE (TPR_ID int, TPR_Title nvarchar (100), TPR_PubDate datetime)
AS
BEGIN
DECLARE @ a varchar (10)
SELECT @ a = xx from xx where xx = @ type
INSERT @ Table select xx, XX, xx from TableName where xx = @ a -- the number of columns defined in Table variables must be consistent with those defined in values.
RETURN
END

For multi-Table operations, you can define table variables in the function body to store the result set and then perform associated queries.
The Scalar Value Function looks like a good one ~~Copy codeThe Code is as follows: create function FUN_DataFormat (@ strDate datetime)
RETURNS varchar (20)
BEGIN

Declare @ date varchar (20)
Set @ date = DATENAME (YY, @ strDate) + 'Year' + Convert (VARCHAR, MONTH (@ strDate) + 'month' + Convert (VARCHAR, DAY (@ strDate) + 'day'
Return @ date
END

Dbo is usually added before the function name when you access a scalar function. Otherwise, it will be regarded as a built-in system function, but an error will be reported because it is not a built-in system function.
The above can be tested in this way.
Select dbo. FUN_DataFormat (getdate ())
You just need to hide it ~~~~~~~