Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2002
    Location
    USA
    Posts
    2

    Unanswered: How to make UDF deterministic

    I have a user defined function in SQL Server 2000 that splits a Name field into First, Last, Middle names. Originally I utilized the CHARINDEX function to find the spaces in the string. Since I then found out that function is by definition Non-deterministic that didn't work for me, so I re-wrote the function a bit differently.

    I am using the function as a field's formula in my table structure. I need the ability to create an index on that field so need the function to be deterministic. Apparently there is still something about how I designed the function that causes it to be non-deterministic. The function code is below; if anyone can help point me in the right direction it'd be greatly appreciated.

    ALTER function fSplitName_test
    (
    @name varchar(100),
    @fname int)
    returns varchar(55)
    as

    begin
    declare @Fname_position int, @Lname_position int, @Mname_position int, @SplitName varchar(55), @iint int, @iLen int,
    @sChar char(1), @sName varchar(55)


    set @name = ltrim(replace(@name, ' JR', ''))
    set @sName = @name
    set @ilen = len(rtrim(@name))
    set @Fname_position = 0
    set @Lname_position = 0
    set @iint = 0

    while @iint <> @ilen
    begin
    set @schar = left(@sName, 1)
    begin
    if @schar = ' '
    begin
    if @Fname_position = 0
    set @Fname_position = @iint
    else
    set @Lname_position = @iint
    end
    set @iint = @iint + 1
    set @sName = right(@sName, @ilen - @iint)
    end
    end
    if @fname = 1
    begin
    if @fname_position <> 0
    set @SplitName = left(@name, @Fname_position)
    else
    set @splitname = 'unkn'
    end
    else
    begin
    if @fname = 2
    begin
    if @lname_position <> 0
    set @SplitName = right(@name, @ilen - @Lname_position-1)
    else
    set @SplitName = right(@name, @ilen - @fname_position)
    end
    else
    begin
    if @fname_position + @lname_position <> @ilen and @lname_position <> 0
    begin
    set @SplitName = left(@name, @lname_position)
    set @splitname = right(@splitname,len(@splitname) - @fname_position - 1)
    end
    else
    set @splitname = 'unkn'
    end
    end
    return @SplitName
    end

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: How to make UDF deterministic

    You might get more response if you post this under the "Microsoft SQL Server" forum. This one's for generic SQL questions - I for one know nothing about SQL Server.

  3. #3
    Join Date
    Oct 2002
    Location
    Philippines
    Posts
    15

    Question Re: How to make UDF deterministic

    what if a person has a lastname like this "DELA CRUZ"
    can you post an example of the name parameter ex: "Michael Angelo Rodriguez" or "Michael Angelo Dela Cruz" coz as you can see on the 2 ex. it is hard to detemine the first name on the first example and last name on the latter the name should have a middle initial or comma perhaps.

  4. #4
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697

    Wild guess...

    This is probably completely wrong, but I wouldn't put it past SQL Server to think that you were trying to modify the parameters. That would, of course, make the function non-deterministic. Try copying them to local variables.

    Otherwise, the way to debug a problem like this is to break your function into small functions and verify that the small functions are all deterministic.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •