T-SQL Programming--user-defined functions (inline table-valued functions)

Inline table-valued functions

Connect to T-SQL programming-user-defined functions (scalar functions)

Http://www.cnblogs.com/viusuangio/p/6212072.html

　　Inline table-valued functions can be used to implement parameterized views (queries), such as having a query whose definition statements are as follows:

1  UseStudent2 GO3 --Check the number of students in the written exam4 SelectS.stuno, S.stuname,s.brithday,e.written,e.lab5  fromStuinfo s Left outer JoinExam e6  onS.stuno=E.stuno7 whereE.written>= -

　　If you want to design a more general-purpose program that allows users to specify query content, you can where E.written >= replaced by where E.written >[email protected] , @para used to pass parameters, but the view query does not support specifying the search condition parameters in the Wherea statement, in order to solve such a problem, you can use inline value functions.

Definition of inline table-valued functions:　　

Create function [owner_name.]function_name ([{@parameter_name [ as]Scalar_parameter_data_type[default]}[, N..]])returns Table [With encryption] [ as]return [(select_stmt)]

　　The meanings of each of these options are as follows:

• owner_name: database owner name
• function_name: The user-defined function name, which must conform to the rules of the identifier, must be unique to its owner in the database.
• @ Parameter_name: The formal parameter name of the user-defined function, create function The statement can declare one or more parameters, with @
• Scalar_parameter_data_type: The data type of the parameter, which can be a basic scalar type supported by the system, cannot be timestamp type, user-defined data type, non-scalar type (such as cursor and tabel).
• Default: specify defaults.
• Returns table: This sentence contains only the keyword table , which indicates that this function returns a table.
• The WITH clause indicates the option to create the function , and if the encryption parameter is indicated, the created function is encrypted and the text of the function definition is stored in an unreadable form in the syscomments table, no one can see the definition of the function, including the creator of the function and the system administrator.
• return [(SELECT_STMT)]: The function body of the inline table-valued function has only one return statement, and the inline value is returned through the SELECT statement specified by the parameter select_stmt .

　　For example , create a query (typically a view) on the student database (assuming it already exists), and define an inline table-valued function based on this query.

1 /*Create a view query, but not with parameters (such as the following comment sentence)*/2 Create ViewVeam as3 SelectS.stuno,s.stuname,s.brithday,e.written,e.lab4  fromStuinfo s Left outer JoinExam e5  onS.stuno=E.stuno6 --where Writtrn >=607 8 9 Ten /*use inline table functions to resolve, pass in parameters, return queries with parameters*/ One Create functionGetexams (@written float) A returns Table  as - return( -     SelectS.stuno,s.stuname,s.brithday,e.written,e.lab the      fromStuinfo s Left outer JoinExam e -      onS.stuno=E.stuno -     whereWritten>= @written -)

　　Calls to inline table-valued functions:

Inline table-valued functions can only be accessed by Select Statement Invocation, when the inline table-valued function is called, you can just use the function name.

For example, calling Getexams () function to check for records with a written test score greater than or equal to 70.

1 Select *  from Dbo.getexams (

T-SQL Programming--user-defined functions (inline table-valued functions)