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)