Return TOP 100 PERCENT WITH TIES from a function : function « Procedure Function « SQL Server / T-SQL Tutorial

3> CREATE TABLE RegionPlace
4>      (RegionID nvarchar (20) NOT NULL ,
5>      TerritoryDescription nchar (50) NOT NULL ,
6>         RegionID int NOT NULL
7> )
8> GO
1>
2> Insert Into RegionPlace Values ('01581','Westboro',1)
3> Insert Into RegionPlace Values ('01730','Bedford',1)
4> Insert Into RegionPlace Values ('01833','Georgetow',1)
5> Insert Into RegionPlace Values ('95060','Santa Cruz',2)
6> Insert Into RegionPlace Values ('98004','Bellevue',2)
7> Insert Into RegionPlace Values ('98052','Redmond',2)
8> Insert Into RegionPlace Values ('98104','Seattle',2)
9> Go

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)
1> CREATE TABLE EmployeeRegion
2>      (EmployeeID int NOT NULL,
3>       RegionID nvarchar (20) NOT NULL
4> )
5> 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> CREATE FUNCTION dbo.udf_EmpRegionPlaceTAB (
3>     @EmployeeID int
4> )   RETURNS TABLE
5> AS RETURN
6> SELECT TOP 100 PERCENT WITH TIES
7>        et.RegionID
8>      , t.TerritoryDescription as [Territory]
9>      , t.RegionID
10>     FROM EmployeeRegion et
11>          LEFT OUTER JOIN RegionPlace t
12>              ON et.RegionID = t.RegionID
13>     WHERE et.EmployeeID = @EmployeeID
14>     ORDER BY t.TerritoryDescription
15>
16> GO
1>
2> drop FUNCTION dbo.udf_EmpRegionPlaceTAB;
3> GO
1> drop table EmployeeRegion;
2> drop table RegionPlace;
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