Creation and invocation of SQL Server custom functions

SQL Server has system-provided functions such as AVG, SUM, GETDATE (), etc., and users can also customize functions.

User-defined functions include scalar and table-valued functions, where scalar functions and system functions are used, and table-valued functions can be divided into nested functions and multi-statement functions according to the way the body is defined.

The syntax is described below.

Scalar functions:

 1  create  function   function name (parameter)  2  returns   return value data type  3  with {Encryption | Schemabinding}    4  [ ]   begin  6  sql statement (return variable)  7  8  Note: Schemabinding binds a function to the object it refers to (note: Once a function is bound, it cannot be deleted, modified, unless the binding is removed) 

View Code

Table-valued functions-inline functions:

1 Create function function name (parameter) 2 returns Table 3 [with{encryption | Schemabinding}]4as5return(one SQL statement)

View Code

Table-valued functions-multi-statement functions:

1 Create function function name (parameter) 2    returns Table (table variable definition) 3    [with{encryption | Schemabinding}]4as5    begin6     SQL statements 7    End

View Code

Here is how to use the method, create several tables for testing before using, the table is as follows:

1 CREATE TABLE [dbo].[Classes](2     [ID] [int] IDENTITY(1,1) not NULL Primary Key,3     [ClassName] [nvarchar]( -) not NULL,4     [Createtime] [datetime]  not NULL5     );6 7     CREATE TABLE [dbo].[Students](8     [ID] [int] IDENTITY(1,1) not NULL Primary Key,9     [Name] [nvarchar]( -) not NULL,Ten     [ClassId] [int]  not NULL, One     [ Age] [int]  not NULL, A     [Createtime] [datetime]  not NULL -     ); -  the     CREATE TABLE [dbo].[Courses]( -     [ID] [int] IDENTITY(1,1) not NULL Primary Key, -     [Name] [nvarchar]( -) not NULL, -     [ Credit] [float]  not NULL +     ); -  +     CREATE TABLE [dbo].[Stuscores]( A     [ID] [int] IDENTITY(1,1) not NULL Primary Key, at     [Stuid] [int]  not NULL, -     [CourseID] [int]  not NULL, -     [score] [int]  not NULL -);

View Code

Examples are as follows:

1 --scalar function: Number of people returning to a class2 Create functionF_getsomeclassstucount (@classId int)3 returns int4  as5 begin6 Declare @rtnCount int7 Select @rtnCount=Count(*) fromStudentswhereClassId=@classId8 return @rtnCount9 End;Ten  One SelectDbo. F_getsomeclassstucount (1); A  - --table-valued functions-inline functions: Return to a class of people note there is no need to begin-end parcels here - Create functionF_getsomeclassstruinfo (@classId int) the returns Table -  as  - return(Select *  fromStudentswhereClassId=@classId); -  + Select *  fromDbo. F_getsomeclassstruinfo (1); -  + --table-valued Function-multi-statement function: Returns a student's score A Create functionF_getsomstuscore (@stuName nvarchar( -)) at returns @tmpTb Table( -Stunamenvarchar( -), -Coursenamenvarchar( -), -Scoreint - ) -  as in begin - Insert  into @tmpTb to SelectS.name asStuname,c.name asCoursename,ss. Score +  fromStuscores SS -  Left JoinStudents s onSs. Stuid=s.id the  Left JoinCourses C onSs. CourseID=c.id * whereS.name=@stuName $ returnPanax Notoginseng End; -  the Select *  fromF_getsomstuscore ('Yang too')

View Code

Creation and invocation of SQL Server custom functions