T-SQL-udfs

User-Defined Functions
User-defined functions cannot execute a series of operations that change the database status. They can be used in queries, stored procedures, and other programs, or execute commands like a process. The User-Defined Function stores a Transact-SQL routine and can return a certain value. Depending on the form of function return values, there are three types of user-defined functions:
(1 ). scalar function: a scalar function returns a scalar value of a specific type. The return value types include text, ntext, image, cursor, timestampt, and table. The function body statement is defined in the begin-end statement, which contains the transact-SQL command that can return values.
Syntax:
Create Function [owner_name] function_name
([{@ Parameter_name [as] scalar_parameter_data_type [= default]} [, N])
Returns scalar_return_data_type
[With <function_option> [, N]
[As]
Begin
Function_body
Return [scalar_expression]
End
Where:
<1>. function_option has two optional values: {encryption | schemabinding}
Encryption: encryption option. enable SQL Server to encrypt the statement of create function in the system table to prevent the release of user-defined functions as part of SQL Server replication.
Schemabinding: plan the binding option. If a user-defined function is bound to a database object referenced by the function, the database objects involved in the function cannot be deleted or modified, unless the function is deleted or removed. Note that the database object to be bound must be in the same database as the function.
<2>. owner_name: Specifies the owner of the User-Defined Function.
<3>. function_name: specify the name of the User-Defined Function.
<4>. database_name.owner_name.function_name must be unique.
<5>. @ parameter_name: defines the names of multiple parameters in a single expression. A function can define a maximum of 1024 parameters. Each parameter is marked with the @ symbol before it. The parameter scope is the entire function, parameters can only replace constants. They cannot represent names, column names, or other database object names. Custom functions do not support output parameters.
<6>. scalar_parameter_data_type: Specify the Data Types of scalar parameters, except for text, ntext, image, cursor, timestampt, and table.
<7>. scalar_return_data_type: Specify the Data Type of the scalar return value, except for text, ntext, image, cursor, timestampt, and table.
<8>. scalar_expression: Specify the scalar value expression returned by the scalar user-defined function.
<9>. function_body: specify a series of transact_ SQL statements that determine the return value of the function.

Example: Create a salary calculation function
Use Taihang
Go
-- Create a function
Create Function workyearwage (@ hiredate datetime, @ today datetime, @ per_wage money)
-- Hiredate indicates the employment period, today indicates the current date, and par_wage indicates the expected salary for each year's length of service
Returns money
As begin
Declare @ workyearwage money
Set @ workyearwage = (Year (@ today)-year (@ hiredate) * @ per_wage
Return (@ workyearwage)
End -- end Function Definition
Go
-- Call a function
Select Taihang. DBO. workyearwage ('-7-1 ', getdate (), 15)
As work_year_wage
Note: The preceding function bodies can be abbreviated:
As begin
Return (Year (@ today)-year (@ hiredate) * @ per_wage)
End
 
(2). nested table value function: return a return value in the form of a table, that is, it returns a table. Nested table-valued functions are not included in the begin-end statement. The returned table is screened out from the database by a SELECT command segment in the return clause. Nested table-valued functions are equivalent to a parameterized view.
Syntax for creating a function:
Create Function [owner_name] function_name
([{@ Parameter_name [as] scalar_parameter_data_type [= default]} [, N])
Returns table
[With <function_option> [, N]
[As]
Return (select-stmt)
Where:
<1>. Table: the batch return value is a table.
<2>. Select-stmt: A single SELECT statement to determine the data of the returned table.
For example, create a company information function that returns information about all purchased products.
Use Taihang
Go
Create Function orderfirms (@ productid varchar (30 ))
-- Productid indicates the product code
Returns table
As
Return (select * from products P
Where p. p_id = @ productid)
Go
 
(3 ). multi-statement table valued functions: can be seen as a combination of scalar and embedded table valued functions. Its return value is a table, but like a scalar function, it has a function body enclosed by the begin-end statement. The data in the returned value table is composed of the function body.
Syntax:
Create Function [owner_name] function_name
([{@ Parameter_name [as] scalar_parameter_data_type [= default]} [, N])
Returns @ return_variable table <table_type_definition>
[With <function_option> [, N]
[As]
Begin
Function_body
Return end
Where:
<1>. <table_type_definition> :( {column_definition | table_constraint} [, N])
<2>. @ return_variable: A table variable used to store and accumulate data rows in the returned table.

4. Modify user-defined functions
Alter function: the syntax of this command is the same as that of create function, which is equivalent to rebuilding.
5. delete user-defined functions
Drop function {[owner_name] function_name} [, N]

V. Functions
In the transact-SQL language, functions are used to execute some special operations to support standard SQL Server commands.
(1). collection function: A collection function can be referenced as a table in a Transact-SQL statement.
(2). Aggregate Function: used to execute Computation for a group of values and return a single value.
(3). scalar function: used to process and compute one or more parameter values passed to it, and return a single value.
(1) classification of scalar functions
1. configuration function: returns the current configuration information.
2. cursor function: returns information about the cursor.
3. Date and Time Functions: This function is used to operate on input values of the date and time types. a substring, number, or date and time value is returned.
4. mathematical functions: used to perform operations on the input values provided as function parameters and return a numeric value.
5. Metadata function: returns information about the database and objects.
6. String function: perform operations on the input value of the string and return the value of a word or number.
7. system functions: perform system operations
8. System statistical function: Return System statistical information.
9. Text and image functions: For text or image input values or columns, operations are performed to return information about these values.

Details:
1. system functions
Returns information about SQL Server systems, users, databases, and database objects. System functions allow users to use conditional statements to perform different operations based on the returned information after obtaining information. Like other functions, you can use system functions in the select and where clauses and expressions of select statements.
For example, the name of the second column in the Yuan table of the Taihang database is returned.
Use Taihang
Select col_name (object_id ('yuanyuan '), 2)
Note: col_name is a system function. object_id: the ID of the returned object.

2. Date and Time types
The date and time functions are used to perform various processing and operations on date and time data, and return a string, numeric value, or Date and Time Value.
Dateadd (datepart, number, date)
Datediff (datepart, date1, date2)
Datename (datepart, date)
Datepart (datepart, date)
Day (date)
Getdate ()
Month (date)
Year (date)
Example 1: extract the number of months from the date returned by the getdate Function
Select datepart (month, getdate () as 'month number'
Note: datepart is a system function.
Example 2: return the month, number of days, and number of years from 03/12/1998
Select month ('03/12/1998 '), Day ('03/12/1998'), Year ('03/12/1998 ')

3. String Functions
Different operations can be performed on binary data, strings, and expressions. Most string functions can only be used for char and varchar data types and explicitly converted to Char and varchar data types, A few string functions can also use binary and varbinary data types. In addition, some strings can also process text, ntext, and image data types.
1. Classification of string functions:
(1). Basic string functions: upper, lower, space, replicate, stuff, reverse, ltrim, rtrim.
(2). string search function: charindex, patindex.
(3). Length and analysis functions: datalength, substring, right.
(4). conversion functions: Asch, Char, STR, soundex, difference
 
4. mathematical functions
It is used to perform mathematical operations on numeric expressions and return calculation results. Mathematical functions can process the numeric data (decimal, integer, float, real, money, samllmoney, smallint, and tinyint) provided by SQL Server.
For example, use the ceiling (rounded up), floor (rounded down to an integer), and round (rounded down to N decimal places) functions in an expression.
Select ceiling (13.4), floor (13.4), round (13.4567, 3)
The result is 14, 13, 13.4570.

5. conversion functions
Generally, SQL Server automatically converts data types. For example, if you compare char and datetime expressions, smallint and INT expressions, or char expressions of different lengths, SQL Server can convert them automatically, which is called implicit conversion. However, if the results cannot be automatically converted by SQL Serve or the results of the automatic conversion by SQL Serve do not meet the expected results, you need to use the Conversion Function for display conversion. There are two conversion functions: Convert and cast.
(1). Cast (expression as data_type)
(2). The couvert function allows the user to convert the reverse expression from one minute's data type to another, and also allows the reverse expression to be converted to different styles.
Convert (data_type [length], expression [, style])
Example:
Use bubs
Select title, ytd_sales from titles
Where cast (ytd_sales as char (20) Like '20140901'
And type = 'trad_cook'