It is not easy to set up function on computed field in temp table. I spent the whole day trying to resolve this… and here is the answer:
use hrnetdev
declare @functions nvarchar(4000)
set @functions = '
use [tempdb]
IF EXISTS( SELECT 1 FROM information_schema.routines WHERE routine_name = ''distance'')
DROP FUNCTION dbo.distance;
SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
exec(''CREATE FUNCTION dbo.distance( @x1 int, @y1 int, @x2 int, @y2 int)
RETURNS decimal(10,3) AS
BEGIN
DECLARE @distance bigint
set @x1 = isnull(@x1, 0)
set @y1 = isnull(@y1, 0)
set @x2 = isnull(@x2, 0)
set @y2 = isnull(@y2, 0)
set @distance = sqrt( (@x1-@x2)*(@x1-@x2) + (@y1-@y2)*(@y1-@y2) )
RETURN @distance
END'')
'
exec(@functions)
select tempdb.dbo.distance(0,5,5,5)