Scalar functions
The RETURNS clause specifies a scalar data type, the function is a scalar-valued function.
Grammar
| Schemabinding}] [as]beginsql statement (must have return variable or value) End
Interpretation:
- 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, and no one can see the definition of the function, including the creator of the function and the system administrator.
- A function question is defined between begin and end, and the function body must include a return statement to return a value. The Scalar_expression function returns the value of an expression.
- The return type of a user-defined function, which can be a basic scalar type supported by SQL Server, except text, NTERXT, image, and timestamp.
Liezi:
int ) --int -- Note that this returns a data type as BEGIN int set3 return @n* @retEND
Table-valued functions are divided into two types: inline table-valued functions (in-line functions) or multi-statement functions
table-valued functions: inline table-valued functions
If the table specified by the RETURNS clause does not come with a list of columns, the function is an inline table-valued function.
Grammar
| Schemabinding}] as return ( one SQL statement)
Interpretation:
- 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, and 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.
Liezi:
floatasreturn( Select s.stuno,s.stuname,s.brithday, E.written,e.lab from stuinfo s left outer joins exam E on s.stuno=E.stuno /c12>where written >= @written)
Table-Valued Functions:multi-statement functions
If the table type specified by the RETURNS clause has a column and its data type, the function is a multi-statement table-valued function
Grammar:
-- multi-sentence table value function Create function name (parameter) returns table variable name table (table variable definition )| Schemabinding}]as begin SQL statement end-a multiple-sentence table-valued function contains multiple SQL statements, at least one of which is populated with data values in a table variable
Example:
Create function GetInfo (@name varchar ( -)) Returns @cTable table (UserName varchar (Ten), Userpwd varchar (Ten)) asbegin INSERT INTO @cTableSelectUsername,userpass fromUserInfowhereUsername=@namereturn -- The last statement in the function must be a return statement. End
--calledSelect* fromGetInfo ('Admin')------------UserName Userpwdadmin Amin
SQL Server Custom Functions