SQL Server Function Summary

I. Function Classification

Functions are classified into scalar functions and table-valued functions. The former returns a scalar value, and the latter returns the result in the form of a table.

Ii. scalar functions

A scalar function accepts 0 or more input parameters and returns a scalar value. Because scalar functions return only one value, they are usually used in the column table of a select statement, or they can be used in the where clause.

Example: create function [dbo]. [ufnGetStock]

(@ ProductID [int])

Returns [int]

As

Begin

Declare @ ret int;

Select @ ret = sum (p. [Quantity]) from [production]. [productInventory] p where p. [ProductID] = @ productID and p. [locationID] = '6 ';

If (@ ret is null)

Set @ ret = 0

Return @ ret

End;

Function:

Select *, dbo. ufnGetStock (Production. Product. ProductID) from Production. Product;

Note: 1. There are some restrictions on functions. functions cannot be used to change any objects in a database or the status of the database. Therefore, you cannot insert, update, or delete data in a table, or create, modify, or delete objects in a database. However, you can create one or more table variables. And issue insert, update, and delete statements for the table variables.

2. a scalar function returns only one value.

Iii. Table value functions

Table valued functions follow the same rules as scalar functions. The difference is that table valued functions return a table as output. Therefore, they are generally used in the from clause of the select statement and may be joined with other tables or views.

Example:

Create function [dbo]. [ufnGetContactInformation]

(

@ ContactID int

)

Return @ retContactInformation table

(

[ContactID] int primary key not null,

[FirstName] [nvarchar] (50) null,

[LastName] [nvarchar] (50) null,

[JobTitle] [nvarchar] (50) null,

[ContactType] [nvarchar] (50) null

)

As

Begin

Declare

@ FirstName [nvarchar] (50 ),

@ LastName [nvarchar] (50 ),

@ JobTitle [nvarchar] (50 ),

@ ContactType [nvarchar] (50 );

Select

@ ContactID = ContactID,

@ FirstName = FirstName,

@ LastName = LastName

From [Person]. [Contact]

Where [ContactID] = @ ContactID;

Set @ JobTitle =

Case

When exists (select * from [HumanResources]. [Employee] e where e. [ContactID] = @ ContactID)

Then (select [Title] from [HumanResources]. [Employee] where [ContactID] = @ ContactID)

When exists (select * from [Purchasing]. [VendorContact] vc inner join [Person]. [ContactType] ct on vc. [ContactTypeID] = ct. [ContactTypeID] where vc. [ContactID] = @ ContactID)

Then (select ct. [Name] from [Purchasing]. [VendorContact] vc inner join [Person]. [ContactType] ct on vc. [ContactTypeID] = ct. [ContactTypeID] where vc. [ContactID] = @ ContactID)

When exists (select * from [Sales]. [StoreContact] SC inner join [Person]. [ContactType] ct on SC. [ContactTypeID] = ct. [ContactTypeID] where SC. [ContactID] = @ ContactID)

Then (select ct. [Name] from [Sales]. [StoreContact] SC inner join [Person]. [ContactType] ct on SC. [ContactTypeID] = ct. [ContactTypeID] where [ContactID] = @ ContactID)

Else null

End;

Set @ ContactType =

Case

When exists (select * from [HumanResoures]. [Employee] e where e. [ContactID] = @ ContactID)

Then 'Employee'

Else null

End;

If (@ ContactID is not null)

Begin

Insert @ retContactInformation

Select @ contactID, @ FirstName, @ LastName, @ JobTitle, @ ContactType;

End;

Return;

End;

Function:

Select * from dbo. ufnGetContactInformation (1 );

Iv. deterministic and non-deterministic Functions

1. deterministic Functions

For the same set of input values, the same value is returned every time a deterministic function is called.

2. Non-deterministic Functions

Different results may be returned each time a non-deterministic function is called. If a function calls a non-deterministic function or the function calls an extended stored procedure, SQL Server also considers the function to be non-deterministic.

3. If a function is uncertain, the result of the function cannot be indexed, either by calling the index on the computing column of the function or by referencing the index view of the function.