Recursion Possible in SQL 2K User Defined Functions?

Recursion Possible in SQL 2K User Defined Functions?

Post by Guy Co » Wed, 06 Feb 2002 13:28:21



The attached recursive procedure will pad out a dotted numeric in the form
of  "1.1" into 0001.0001 so that 1.13 will sort after 1.3 (e.g. 0001.0003,
0001.0013)

The algorith is correct ( the corresponding VB code is supplied in the
comments).. It appears that the function goes brain dead when the recursive
term is applied to the outer loop.  I know there a db config for recursive
triggers and I seem to recall something about recursive functions but can't
find a thing in the docs... Pointers to solving this problem would really be
terrific.

----- snip ---

if exists ( select * from sysobjects where name = N'InsertZeros')
 drop function InsertZeros
go

--Private Function InsertZeros(original As String) As String
--Dim nPos As Integer         ' Start converting here
--Dim sRemainder As String    ' String remaining to convert
--Dim sToPad As String        ' The string to pad
--Dim nLenToPad As Integer    ' The length of the string to pad
--Dim zeros As String         ' Holds the 0es to add to the string
--Dim Finished As String
--
--nPos = InStr(1, original, ".")
--If Len(original) > 0 Then
--    If nPos > 0 Then                        ' Then there a decimal in the
string
--        sToPad = Left(original, nPos - 1)
--        sRemainder = Mid(original, nPos + 1)
--    Else
--        sToPad = original                   'There is no decimal
--        sRemainder = ""
--    End If
--    nLenToPad = MAXPlaces - Len(sToPad)
--    zeros = String(nLenToPad, "0")
--    Finished = zeros + sToPad
--    Finished = Finished + "." + InsertZeros(sRemainder)
--End If
--    InsertZeros = Finished
--End Function

-- The function pads out each portion of a dotted numeric string (1.1.1.1)
to the length specified by MAXPLACES so
-- that sets of numeric strings sort in the proper order : 1.1, 1,2, 1.12
would be returned as
-- 0001.0001, 0001.0002, 0001.0012
-- when the dotted portions are padded, they can be sorted into the proper
order...
CREATE FUNCTION dbo.InsertZeros

AS
BEGIN












 begin

   begin


   end
  else
   begin


   end



  /* RETURNS A NULL AFTER THE RECURSION

  */
  -- When the recursion is removed the function returns the 1st part of the
data.

 end

END
GO

--select * from dbo.InsertZeros('1.1')

 
 
 

Recursion Possible in SQL 2K User Defined Functions?

Post by Umachandar Jayachandra » Wed, 06 Feb 2002 15:24:28


    Note if you make recursive calls, you are limited by 32 I think in
SQL2000. You can write this function without recursion like:

create function InsertZeroes (

)
returns varchar(8000)
begin




    while ( 1 = 1 )
    begin





    end

end
go
print dbo.InsertZeroes( '1.1' )
print dbo.InsertZeroes( '1.12' )
print dbo.InsertZeroes( '123.1234' )

--
Umachandar Jayachandran
SQL Resources at http://www.umachandar.com/resources.htm
( Please reply only to newsgroup. )