Database
Enthusiast

## coverting a SQL Server function into a TD SQL UDF

Hi guru's,

I am faced with a problem of converting a SQL Server function into a TD SQL UDF. As we are all aware that Teradata is not flexible with the usage of "set/declare/loop" etc etc and I am wondering if there is a way to still code this with the limited functionality. The function I am trying to convert is as below and I am on TD 13.10 db version. Although we can easily do this in a Store procedure, it does not solve my purpose. I thought of recursive SQL, but not sure if I can do this. Your help is greatly appreciated.

CREATE FUNCTION [dbo].[Symmetrical_Probability_Approximation]

(

@N int,

@precision real,

@distance real

)

RETURNS real

AS

BEGIN

declare @sigma real;

declare @AP real;

declare @result real;

declare @OPj real;

declare @OPi real;

declare @Aj real;

declare @Ai real;

declare @pj real;

declare @pi real;

declare @i int;

declare @Ri real;

set @sigma=@precision/2.146;

set @OPj=0.0;

set @Aj=0.0;

set @pj=0.0;

set @result=0.0;

set @i=0;

While (@i<@N) Begin

set @i=@i+1;

set @Ri=(1.5*@i*@precision)/@N;

set @Ai=3.14159265*@Ri*@Ri;

set @pi=1-exp(-(@Ri*@Ri)/(2*@sigma*@sigma));

if (@Ri+@radius<=@distance) set @OPi=0;

else if (@distance+@radius<=@Ri) begin

set @OPi=@AP;

set @i=@N;

end

else if (@distance+@Ri<=@radius) set @OPi=@Ai;

set @result=@result+(@pi - @pj)*(@OPi-@OPj)/(@Ai-@Aj);

set @OPj=@OPi;

set @Aj=@Ai;

set @pj=@pi;

end

RETURN @result;

END

Thank you.