Function in computed field in temp table

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)
This entry was posted in Uncategorized and tagged , , . Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *