Database Reference
In-Depth Information
from dbo.POI
where
@Lat between MinLat and MaxLat and
@Lon between MinLon and MaxLon
for xml path('POI'), root('POIS')
)
insert into @Result(POIID, POIName, IDList) values(@POIID, @POIName, @IDList)
end
return
end
As you see, there are two separate queries against the table in the implementation. If you want to convert this
function to an inline table-valued function, you can run the queries either as two CTEs or as subselects and
cross-join their results. The
If @ReturnList
=
1
statement can be replaced with the
CASE
operator, as you can
see in the implementation shown in Listing 10-17.
Listing 10-17.
Converting multi-statement to inline functions: Inline function implementation
create function dbo.GetPOIInfoInline(@Lat decimal(9,6), @Lon decimal(9,6), @ReturnList bit)
returns table
as
return
(
with TopPOI(POIID, POIName)
as
(
select top 1 POIID, Name
from dbo.POI
where @Lat between MinLat and MaxLat and @Lon between MinLon and MaxLon
order by Name
)
,IDList(IDList)
as
(
select
case
when @ReturnList=1
then
(
select POIID as [@POIID]
from dbo.POI
where
@Lat between MinLat and MaxLat and
@Lon between MinLon and MaxLon
for xml path('POI'), root('POIS'), type
)
else null
end
)
select TopPOI.POIID, TopPOI.POIName, IDList.IDList
from TopPOI cross join IDList
)
