SQL Server stored procedures, triggers, custom functions (ii)

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)