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
endYou 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.|||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.|||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.sql

How to make argument/parameter optional in UDF/Trigger?

Hello,
I took over a project, and I need to make updates, modifications, pick up
where the last person left off. There is one table that is used in several
processess from external apps. This table contains an InsteadOf trigger tha
t
I need to disable for just one new procedure that am adding to the project.
I am thinking of using a UDF where I pass a value to the UDF and that value
disables the trigger. But the other processes/procedures which are already
in place obviously do not have a call to this UDF, and I sure don't want to
spend the time hunting down every process/procedure that uses this table (al
l
the external apps). Or alternatively, is there a way to create an optional
parameter in the trigger - where if a value is passed in, the trigger gets
disabled, else trigger does its thing. The other processes/procedures woul
d
not pass in this optional parameter. Is there a way to do this?
Thanks,
RichI think I am getting discombobulated. I don't think you can pass things in
to Triggers. Can you?
I am thinking I could set a value in a support table that only my procedure
would know about and then remove that value from the table when the process
is complete.
"Rich" wrote:

> Hello,
> I took over a project, and I need to make updates, modifications, pick up
> where the last person left off. There is one table that is used in severa
l
> processess from external apps. This table contains an InsteadOf trigger t
hat
> I need to disable for just one new procedure that am adding to the projec
t.
> I am thinking of using a UDF where I pass a value to the UDF and that val
ue
> disables the trigger. But the other processes/procedures which are alread
y
> in place obviously do not have a call to this UDF, and I sure don't want t
o
> spend the time hunting down every process/procedure that uses this table (
all
> the external apps). Or alternatively, is there a way to create an option
al
> parameter in the trigger - where if a value is passed in, the trigger gets
> disabled, else trigger does its thing. The other processes/procedures wo
uld
> not pass in this optional parameter. Is there a way to do this?
> Thanks,
> Rich