SQL Server stored procedures, triggers, custom functions:
Custom functions:
1. function type;
2. Parameters and return values of the function;
1. Function Type:
A scalar-valued function that returns a scalar value
Table-Valued Functions:
Inline table-valued functions;
A multi-statement table-valued function.
Scalar-valued Functions:
1 Go2 Create functionSumorders (@ Employee Number varchar( -))--Specify the parameter name, and return type Stuno3 returns int --specifying the return type4 begin 5 Declare @ Total number of orders int --number of students sumstudent6 Select @ Total number of orders=Count(Order. Order number) from 7OrderJoinWorkers onOrder. Employee number=Employees . Employee Number8 whereEmployees. Employee number=@ Employee Number 9 return @ Total number of orders Ten End One A SelectDbo. Sumorders ('E4') - - Go the Alter functionSumstudent (@stuNo int)--Specify the parameter name, and return type Stuno - returns int --specifying the return type - begin - Declare @sumS int --number of students sumstudent + Select @sumS=Count(*) fromStuinfo - whereStuno>=@stuNo + return @sumS A End at Go - SelectDbo. Sumstudent (4) asTotal
--EG2: Creating an inline table-valued function
1 Create functionSelectordersbytime (@ start Time datetime,@ end Time datetime) 2 returns Table 3 return Select * fromOrderwhere 4Order Datebetween @ start Time and @ end Time 5 6 Go7 Select * fromSelectordersbytime ('2003-01-01','2003-07-01') 8 --=======================9 GoTen Create functionSelectinfobytime (@ start Time int,@ end Time int) One returns Table A return Select * fromStuinfowhere -Stunobetween @ start Time and @ end Time - the Go - Select * fromSelectinfobytime (1,9)
--EG3: Creating a multi-statement table-valued function
1 Create functionMingdan ()--no parameter function2 returns @ list Table 3 ( 4Numberint Identity(1,1) not NULL, 5Namenvarchar(Ten) not NULL 6 ) 7 begin 8 Insert @ list 9 SelectVendor number, name fromsupplierTen Insert @ list One SelectEmployee number, name fromStaff A return - End - Go the Select * fromMingdan () - - --EG4: Custom function generates default values: - Go + Create functionDefault_num () - returns varchar(7) + begin A Declare @ number varchar(7) at Declare @id int - Select Top 1 @ number=Number fromTestorder byNumberdesc - if @ @rowcount=0 - Set @ number='tcp_001' - Else - begin in Set @id=cast(substring(@ number,5,3) asint)+ 1 - Set @ number='Tcp_' + Replicate('0',3-Len(@id))+cast(@idasvarchar(3)) to End + return @ number - End
--EG4: Custom function generates default values:
1 Create functionDefault_num ()2 returns varchar(7)3 begin4 Declare @ number varchar(7)5 Declare @id int6 Select Top 1 @ number=Number fromTestorder byNumberdesc7 if @ @rowcount=08 Set @ number='tcp_001'9 ElseTen begin One Set @id=cast(substring(@ number,5,3) asint)+ 1 A Set @ number='Tcp_' + Replicate('0',3-Len(@id))+cast(@idasvarchar(3)) - End - return @ number the End
SQL Server stored procedures, triggers, custom functions (ii)