SQL Server Functions

A function is a subroutine composed of one or more statements. It can be used to encapsulate code for reuse.

 

System FunctionsGroupBuilt-in functionsTo perform operations on values, objects, and settings in SQL Server, and return information about them.

 

SQL Server basic functions:

1. String Functions
Length and Analysis
Datalength (char_expr) returns the number of characters in the string, but does not contain spaces
Substring (expression, start, length ).
Right (char_expr, int_expr) returns int_expr characters to the right of the string

Character operation class
Convert upper (char_expr) to uppercase
Lower (char_expr) to lowercase
Space (int_expr) generates int_expr Spaces
Replicate (char_expr, int_expr) copies the string int_expr times
Reverse (char_expr) reverse string
Stuff (char_expr1, start, length, char_expr2) Replace the length characters starting from start in character char_expr1 with char_expr2
Ltrim (char_expr) rtrim (char_expr) removes Spaces
The ASCII (char) Char (ASCII) function corresponds to each other, and the ASCII code is used.

String search
Charindex (char_expr, expression) returns the starting position of char_expr
Patindex ("% pattern %", expression) returns the starting position of the specified mode. Otherwise, it is 0.

2. mathematical functions
ABS (numeric_expr) returns the absolute value
Ceiling (numeric_expr) is the smallest integer greater than or equal to the specified value.
Exp (float_expr) returns an index
Floor (numeric_expr) is less than or equal to the value of the specified maximum integer
Pi () 1415926 .........
Power (numeric_expr, power) returns power to power
Rand ([int_expr]) random number generator
Round (numeric_expr, int_expr) the precision specified by anint_expr is rounded
Sign (int_expr) returns + 1, 0,-1 based on positive number, 0, negative number.
SQRT (float_expr) Square Root

3. Date Functions
Getdate () returns the date
Datename (datepart, date_expr) returns a name such as June
Datepart (datepart, date_expr) is a part of the date.
Datediff (datepart, date_expr1.dateexpr2) date difference
Dateadd (datepart, number, date_expr)

In the preceding functions
Value and meaning of writing
YY 1753-March
QQ: 1-4
Mm 1-March December
Dy 1-366
DD 1-31
WK 1-54 weeks
DW 1-7 weeks
HH 0-23 hours
Mi 0-59 minutes
SS 0-59 seconds
MS 0-999 Ms

Date Conversion
Convert ()

4. system functions
Suser_name () User Login Name
User_name () user inDatabaseName in
The userDatabaseName in
The rule that show_role () applies to the current user.
Db_name ()DatabaseName
Object_name (obj_id)DatabaseObject Name
Col_name (obj_id, col_id) column name
Col_length (objname, colname) Column Length
Whether valid_name (char_expr) is a valid identifier

 

 

SQL Server does not limit users to built-in functions defined as part of the transact-SQL language, but allows users to create their own user-defined functions.

You can use the create function statement to create, use the alter function statement to modify, and use the drop function statement to remove user-defined functions. Each fully valid user-defined function name (database_name.owner_name.function_name) must be unique.

You must be granted the create function permission to create, modify, or remove user-defined functions. Before using a function in a Transact-SQL statement, a user who is not the owner must first grant the appropriate permissions to the function. To create or modify a table that references a user-defined function in the check constraint, default clause, or calculation column definition, you must have the references permission of the function.

In functions, differentiated processing leads to the deletion of statements and the subsequent statements in modes such as triggers or stored procedures are in a Transact-SQL error. In the function, the above error will cause the function to stop being executed. Next, this operation stops the wake-up statement that calls the function.

User-Defined Function Type

SQL Server 2000 supports three types of user-defined functions:

　　Scalar functions

　　Embedded Table value functions

　　Multi-statement Table value functions

User-Defined Functions use zero or more input parameters and return scalar values or tables. A function can have up to 1024 input parameters. When a function parameter has a default value, you must specify the default keyword when calling the function to obtain the default value. This behavior is different from parameters that contain default values in stored procedures. Omitting this function in these stored procedures also means omitting the default value. User-defined functions do not support output parameters.

The scalar function returns a single data value of the type defined in the returns clause. All scalar data types can be used, including bigint and SQL _variant. The timestamp data type, user-defined data type, and non-standard data type (such as table or cursor) are not supported ). The function body defined in the begin... end block contains the series of transact-SQL statements that return this value. The return type can be any data type except text, ntext, image, cursor, and timestamp.

The table value function returns table. For nested table valued functions, there is no function subject; the table is the result set of a single SELECT statement. For multi-statement table-valued functions, the body of the function defined in the begin... end block contains TRANSACT-SQL statements that generate rows and insert rows into the table that will be returned. For more information about embedded Table value functions, see embedded user-defined functions. For more information about table value functions, see user-defined functions that return table data types.

The statements in the begin... end block cannot have any side effects. Function side effects refer to any permanent changes to the state of resources with a function out-of-function scope (such as changes to database tables. The only change that can be made by a statement in a function is a change to a local object (such as a local cursor or local variable) on the function. Operations that cannot be performed in the function include modifying the database table, performing operations on partial cursors not on the function, sending an email, and attempting to modify the directory, and generate the result set returned to the user.

The valid statement types in the function include:

Declare statement, which can be used to define local data variables and cursors of a function.

Assign values to local objects of a function. For example, assign values to scalar and table local variables using set.

A cursor operation that references a local cursor declared, opened, closed, and released in a function. The fetch statement cannot be used to return data to the client. Only the fetch statement can be used to assign values to local variables through the into clause.

Control Flow statement.

Select statement, which contains a selection list with an expression. The expression in the list grants the value to the local variable of the function.

Insert, update, and delete statements. These statements modify the local table variables of the function.

Execute statement, which calls the extended stored procedure.

The actual number of executions of the function specified in the query may vary among the execution plans generated by the optimizer. In this example, the subquery in the where clause wakes up the called function. The number of subqueries and their function executions varies depending on the access path selected by the optimizer.

Built-in functions that return different data for each call are not allowed in user-defined functions. The following built-in functions are not allowed in user-defined functions:

@ Connections @ pack_sent getdate

@ Cpu_busy @ packet_errors getutcdate

@ Idle @ timeticks newid

@ Io_busy @ total_errors Rand

@ Max_connections @ total_read textptr

@ Pack_received @ total_write

Architecture binding function

Create Function supports the schemabinding clause, which can bind a function to the architecture of any objects referenced by it (such as tables, views, and other user-defined functions. An attempt to execute alter or drop on any object referenced by the schema binding function fails.

Schemabinding can be specified in create function only if the following conditions are met:

All views and user-defined functions referenced by this function must be bound to the architecture.

All objects referenced by this function must be in the same database as the function. The object must be referenced by a name consisting of one or two parts.

You must have the references permission on all objects referenced in the function (tables, views, and user-defined functions.

You can use alter function to delete schema bindings. The alter function statement will redefine the function without the specified function with schemabinding.

Call User-Defined Functions

When calling a scalar user-defined function, the name must contain at least two parts:

Select *, myuser. myscalarfunction ()

From mytable

You can call the table value function with a part name:

Select *

From mytablefunction ()

However, when calling the SQL Server built-in function of the returned table, you must add the prefix: To the function name:

Select * From: fn_helpcollations ()

Scalar functions can be referenced anywhere in the expression of the same data type returned by functions allowed in the transact-SQL statement, including the calculation column and check constraint definitions. For example, the following statement creates a simple function that returns decimal:

Create Function cubicvolume

-- Input dimensions in centimeters

(@ Cubelength decimal (4, 1), @ cubewidth decimal (4, 1 ),

@ Cubeheight decimal (4, 1 ))

Returns decimal (12, 3) -- cubic centimeters.

As

Begin

Return (@ cubelength * @ cubewidth * @ cubeheight)

End

You can then use this function wherever an integer expression is allowed (such as in the calculation column of a table:

Create Table bricks

(

Brickpartnmbr int primary key,

Brickcolor nchar (20 ),

Brickheight decimal (4, 1 ),

Bricklength decimal (4, 1 ),

Brickwidth decimal (4, 1 ),

Brickvolume

(

DBO. cubicvolume (brickheight,

Bricklength, brickwidth)

)

)

DBO. cubicvolume is an example of a user-defined function that returns scalar values. The returns clause defines the scalar Data Type of the value returned by the function. The begin... end block contains one or more Transact-SQL statements that execute the function. Each return statement in this function must have a parameter to return the data type specified in the returns clause (or implicitly converted to the data type specified in returns). The return parameter value is the value returned by the function.