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