SQL Server Custom Functions

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