Some cases may use the following table-valued function, the table-valued function is mainly used for data calculation to return the result set, can take parameters (and a large difference of view), if the function does not have too much logic processing, such as the definition of variables, judgments and so on,
the table-valued function returns a result set that can be simply written as follows:
Copy Code code 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 called
If you want to define variables in a function, to determine the calculation of what to do, the wording is a little different, to define the table variables, the table value function is not allowed to create temporary tables, can only be table variables.
Give a simple style of writing, as follows:
Copy Code code as follows:
CREATE FUNCTION fun_getinfolist (@type varchar (10))
RETURNS @Table Table (tpr_id int,tpr_title nvarchar (MB), 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 the table variable is the same as the number of values
Return
End
If you do a multiple-table operation, you can define table variables in the function body to hold the result set and then associate the query.
Scalar value function also paste a look good, the old, hehe ~ ~
Copy Code code as follows:
CREATE FUNCTION Fun_dataformat (@strDate datetime)
RETURNS varchar () as
BEGIN
DECLARE @date varchar (20)
Set @date = Datename (YY, @strDate) + ' year ' +convert (Varchar,month (@strDate)) + ' month ' +convert (Varchar,day (@strDate)) + ' Day '
Return @date
End
When accessing a scalar-valued function, it is usually added to the dbo before the function name, or it is considered to be a built-in function of the system, but is not an error because it is not a system built-in function.
the above can be tested like this.
SELECT dbo. Fun_dataformat (GETDATE ())
That's the trick, ~~~~~~~.
