Get the 3 employees with the most RegionPlace : function « Procedure Function « SQL Server / T-SQL Tutorial

2>
3>
4> CREATE TABLE EmployeeRegion
5>      (EmployeeID int NOT NULL,
6>       RegionID nvarchar (20) NOT NULL
7> )
8> GO
1>
2> Insert Into EmployeeRegion Values (1,'06897')
3> Insert Into EmployeeRegion Values (1,'19713')
4> Insert Into EmployeeRegion Values (9,'48084')
5> Insert Into EmployeeRegion Values (9,'48304')
6> Insert Into EmployeeRegion Values (9,'55113')
7> Insert Into EmployeeRegion Values (9,'55439')
8> GO

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)
1>
2>
3> CREATE TABLE Employees (
4>      EmployeeID int NOT NULL ,
5>      LastName nvarchar (20) NOT NULL ,
6>      FirstName nvarchar (10) NOT NULL ,
7>      Title nvarchar (30) NULL ,
8>      TitleOfCourtesy nvarchar (25) NULL ,
9>      BirthDate datetime NULL ,
10>     HireDate datetime NULL ,
11>     Address nvarchar (60) NULL ,
12>     City nvarchar (15) NULL ,
13>     Region nvarchar (15) NULL ,
14>     PostalCode nvarchar (10) NULL ,
15>     Country nvarchar (15) NULL ,
16>     HomePhone nvarchar (24) NULL ,
17>     Extension nvarchar (4) NULL ,
18>     Photo image NULL ,
19>     Notes ntext NULL ,
20>     ReportsTo int NULL ,
21>     PhotoPath nvarchar (255) NULL
22>
23> )
24> GO
1>
2>
3> CREATE FUNCTION dbo.udf_EmpTerritoryCOUNT (
4>     @EmployeeID int
5> )   RETURNS INT
6> AS BEGIN
7>     DECLARE @RegionPlace int
8>     SELECT @RegionPlace = count(*)
9>         FROM EmployeeRegion
10>         WHERE EmployeeID = @EmployeeID
11>
12>     RETURN @RegionPlace
13> END
14> GO
1>
2>
3> 
4> SELECT TOP 3 LastName, FirstName
5> , dbo.udf_EmpTerritoryCOUNT(EmployeeID) as RegionPlace
6>     FROM Employees
7>     WHERE dbo.udf_EmpTerritoryCOUNT(EmployeeID) > 3
8>     ORDER BY dbo.udf_EmpTerritoryCOUNT(EmployeeID) desc
9> GO
LastName             FirstName  RegionPlace
-------------------- ---------- -----------

(0 rows affected)
1>
2>
3> drop FUNCTION dbo.udf_EmpTerritoryCOUNT;
4> GO
1>
2>
3> drop table EmployeeRegion;
4> GO
1>
2> drop table Employees;
3> GO

21.2.function
	21.2.1.	The syntax for creating a multi-statement table-valued function
	21.2.2.	The syntax for creating a scalar-valued function
	21.2.3.	The syntax for creating a simple table-valued function
	21.2.4.	The syntax of the ALTER FUNCTION statement for a scalar valued function
	21.2.5.	The syntax for altering a simple table-valued function
	21.2.6.	The syntax for altering a multi-statement table-valued function
	21.2.7.	Call user-defined function in where clause
	21.2.8.	Call two user-defined functions in a select statement
	21.2.9.	Function WITH SCHEMABINDING
	21.2.10.	Multistatement Table-Valued Functions
	21.2.11.	Pass a declared variable to a function
	21.2.12.	Return TOP 100 PERCENT WITH TIES from a function
	21.2.13.	Get the 3 employees with the most RegionPlace
	21.2.14.	Use function as a view
	21.2.15.	Create a scalar-valued function that returns the total Billing amount due
	21.2.16.	Invoke the scalar-valued function
	21.2.17.	A SELECT statement that uses the function in a join operation
	21.2.18.	Use function to wrap a long sql statement
	21.2.19.	Query a table returned from a function