Mega Code Archive
Return TOP 100 PERCENT WITH TIES from a function
<![CDATA[
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
]]>