MS SQL Server Table Functions

Stored procedures are made to destroy boundaries, but at least we have table functions now which at least give some modicum to respecting the data i.e. there are inputs and there are outputs instead of ref or out parameters.

Use a table function with a cross apply instead of a join because of table functions

SELECT ts.* FROM [dbo].[theTable] t\nCROSS APPLY dbo.zzz(t.MI_PRINX) ts

Table function

CREATE FUNCTION [dbo].[zzz](@targetId int)
RETURNS @targetData TABLE 
(
 -- Columns returned by the function
 TargetArea int NOT NULL, 
     TableName varchar(255) NOT NULL,
     Id int NOT NULL,
     [Name] varchar(255) NULL
)
AS
BEGIN
     DECLARE @TempTable table (TableName varchar(255) NOT NULL, Id int NOT NULL, [Name] varchar(255) NULL)

     INSERT INTO @TempTable
     select top 1 'Site', s.site_id, s.[name]
     from [dbo].[theTable] as t
     inner join [dbo].[otherTable] as s
     on s.SP_GEOMETRY.STIntersects(t.SP_GEOMETRY) = 1
     where t.MI_PRINX = @targetId     

     INSERT INTO @targetData (TargetArea, TableName, Id, [Name])
     SELECT @targetId, TableName, Id, [Name]
     FROM @TempTable
RETURN
END
GO