Creation and invocation of SQL Server custom functions

Source: Internet
Author: User
Tags scalar

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

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.