Summary of methods for customizing functions in SQL Server

Source: Internet
Author: User
Tags copy functions sql variables return scalar variable
"Custom Functions" is our usual term, and "user-defined Functions" is a written version of SQL Server.

SQL Server 2000 allows users to create custom functions, and custom functions can have return values.

Custom functions are divided into: scalar-valued functions or table-valued functions

If the RETURNS clause specifies a scalar data type, the function is a scalar-valued function. You can use multiple Transact-SQL statements to define scalar-valued functions.
If the RETURNS clause specifies table, the function is a table-valued function.
Table-valued functions can be divided into: inline table-valued functions (in-line functions) or multiple-statement functions

If the table specified by the RETURNS clause does not contain a list of columns, the function is an inline table-valued function.
If the table type specified by the RETURNS clause has a column and its data type, the function is a multiple-statement table-valued function.
example of a scalar-valued function
Copy CodeThe code is as follows:
The CREATE FUNCTION dbo. Foo ()
RETURNS int
As
BEGIN
DECLARE @n int
Select @n=3
Return @n
End

Inline table-valued functions Example
Copy CodeThe code is as follows:
The CREATE FUNCTION dbo. Foo ()
RETURNS TABLE
As
Return select ID, title from msgs

Inline table-valued functions have only one SELECT statement.

example of a multiple-statement table-valued function (partial)
Copy CodeThe code is as follows:
CREATE FUNCTION fn_findreports (@InEmpId nchar (5))
RETURNS @retFindReports TABLE (empid nchar (5) Primary key,
EmpName nvarchar (m) not NULL,
Mgrid nchar (5),
Title nvarchar (30))
...

Notice the RETURNS part of it.

The following statement is allowed in the body of a multiple-statement function. Statements that are not listed in the following list cannot be used in the body of a function.

An assignment statement.
Control flow statements.
DECLARE statement that defines the data variables and cursors for the local function.
SELECT statement, which contains a selection list with an expression that assigns a value to a function's local variable.
A cursor operation that references a local cursor declared, opened, closed, and disposed in a function. Only fetch statements that use an INTO clause to assign values to a local variable are allowed, and FETCH statements that return data to the client are not allowed.
INSERT, UPDATE, and DELETE statements, which modify the local table variables of the function.
The EXECUTE statement invokes the extended stored procedure.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.