SQL 2000 User-Defined Functions

I. Three udfs of SQL Server

"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

Create function dbo. Foo ()
RETURNS int
AS
BEGIN
Declare @ n int
Select @ n = 3
Return @ n
END

Create Function getsex (@ sex INT)
Returns varchar (2) -- use nvarchar (1)
As -- nvarchar: both a single character and a Chinese character are saved as 2 bytes.
Begin -- varchar: the Chinese character is two bytes, and other characters are saved as one byte.
Declare @ ssex varchar (2) -- use nvarchar (1)
If (@ sex = 1)
Set @ ssex = 'male'
Else
Set @ ssex = 'female'
Return (@ ssex)
End
Call:
Select id, username, dbo. GetSex (Sex) as sex, loginnum from t_user

Nested table value function example

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)

Create function FindUsers (@ userid int)
RETURNS @ FindUser TABLE (id int primary key, UserName varchar (30), RegDateTime DateTime, LoginNum int)
As
Begin
Insert into @ FindUser Select ID, UserName, RegDateTime, LoginNum From T_User Where ID = @ userid
Return
End
Call:
SELECT * FROM FindUsers (6)

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.

Ii. How to call udfs

 

The method for calling a user-defined function is basically the same as that for calling a built-in function,

• When calling a scalar function, you must add "owner", which is usually DBO (but not absolute. You can view the owner in "user-defined functions" in Enterprise Manager ).
• When calling a table-valued function, you can only use the function name.

Example

SELECT *
Select dbo. Sta (cnt) from tbl

Example

SELECT *
From fn_mytablefunc (123.09, N 'O' 'neill') 3. Differences between stored procedures and user-defined functions

The differences between stored procedures (User-Defined stored procedures) and user-defined functions (User-Defined Functions) have been discussed on the Internet, but some seem too fragmented, some of them are too long to ensure that everyone can read them carefully. Here is an induction, of course, only at the application layer, which may not be comprehensive.

• Stored procedures are powerful. They can perform a series of database operations, such as modifying tables, or create stored procedures that run automatically when SQL Server is started.
• User-defined functions cannot be used to modify the status of a global database.

 

• Stored Procedures. You can use undefined functions.
• Udfs are not allowed to contain udfs.

 

• Stored Procedure, which can return the record set.
• A user-defined function that returns table variables.

 

• Stored Procedure, and its return value cannot be directly referenced.
• The return value of a custom function can be directly referenced.

 

• Stored Procedure, which is executed using the execute statement.
• Custom function, called in the query statement.

Come: http://www.cftea.com/c/805.asp