Summary of methods for udfs in sqlserver

Source: Internet
Author: User

"User-defined functions" are common statements, while "user-defined functions" are written in SQL Server.

SQL Server 2000 allows users to create user-defined functions. User-defined functions can return values.

User-Defined Functions are classified into scalar functions or table-valued functions.

If the RETURNS clause specifies a scalar data type, the function is a scalar value function. You can use multiple Transact-SQL statements to define scalar value functions.
If the RETURNS clause specifies a TABLE, the function is a TABLE value function.
Table valued functions can also be divided into: Embedded table valued functions (intra-row functions) or multi-statement Functions

If the TABLE specified by the RETURNS clause does not contain a list of columns, this function is an embedded TABLE value function.
If the TABLE type specified by the RETURNS clause contains columns and their data types, this function is a multi-statement TABLE value function.
Scalar function example
Copy codeThe Code is as follows:
Create function dbo. Foo ()
RETURNS int
AS
BEGIN
Declare @ n int
Select @ n = 3
Return @ n
END

Nested table value function example
Copy codeThe Code is as follows:
Create function dbo. Foo ()
RETURNS TABLE
AS
Return select id, title from msgs

The nested table value function has only one select statement.

Multi-statement Table value function example (Part)
Copy codeThe Code is as follows:
Create function fn_FindReports (@ InEmpId nchar (5 ))
RETURNS @ retFindReports TABLE (empid nchar (5) primary key,
Empname nvarchar (50) not null,
Mgrid nchar (5 ),
Title nvarchar (30 ))
...

Pay attention to its RETURNS section.

The following statements can be used in the body of a Multi-statement function. Statements not listed in the following list cannot be used in the function body.

Value assignment statement.
Control Flow statement.
DECLARE statement, which defines the local data variables and cursors of the function.
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.
A cursor operation that references a local cursor declared, opened, closed, and released in a function. Only FETCH statements that assign values to local variables using the INTO clause are allowed. FETCH statements that return data to the client are not allowed.
INSERT, UPDATE, and DELETE statements. These statements modify the local table variables of the function.
EXECUTE statements call extended stored procedures.

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.