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 this join select statement works

Here is a select statement of table A:

select user01_id, user02_id from A where ...

I also need to have the names of users which is in the table user. How to join the table user to have the name field and yeild two fields with the same name?

select a.user01_id,u.name, a.user02_id, u.name from a join user u on (?)

table A

user01_id int,
user02_id int,
...
primary (user01_id, user02_id)

table user

userid int primary key,
name varchar(80),
...

Thans for your advise.select user01_id, u01.name as name01
, user02_id, u02.name as name02
from A
left outer
join user u01
on user01_id = u01.userid
left outer
join user u02
on user02_id = u02.useridrudy
http://r937.com/|||Originally posted by r937
select user01_id, u01.name as name01
, user02_id, u02.name as name02
from A
left outer
join user u01
on user01_id = u01.userid
left outer
join user u02
on user02_id = u02.useridrudy
http://r937.com/

Hi, Rudy,

The query works well. I didn't know the alias also can be appled on a field in addition of a table. I guess I can use inner join instead when the both user01_id and user02_id of table A refer to the userid in the table user.

Thanks very much for your help.

Vernon|||yes, you could use inner joins, if you are guaranteed that the userids will exist

i guess i use left outer simply because it is "defensive sql" -- if either of the userids doesn't match, the A row disappears...

rudy|||Originally posted by r937
yes, you could use inner joins, if you are guaranteed that the userids will exist

i guess i use left outer simply because it is "defensive sql" -- if either of the userids doesn't match, the A row disappears...

rudy

In fact, the two IDs of table A are foreign keys of the tabe user primary key, userid. So existence of the userid is guaranteed.

A good usage of "defensive". That is the difference between a master and a regular craftsman.

how to make column lowercase

I have a table that contains names that are all in upper case, this column is called in many different areas of my web app. I wanted to make the names all lowercase, or with the leading character only capitalized.
How can I make a column within a SQL table lowercase at the SQL server end and not the programming side?

thanks,
Frank

UPDATE
 TableName
SET
 FieldName = LCASE(FieldName)

For all future entries, you create a trigger that will execute on insert.|||If you are talking about the data to be converted in lowercase, then following is the syntax:

select lower(column_name) from table_name

To convert first letter in Capital, you could write your own function, using UPPER, LOWER, SUBSTRING and CHARINDEX.
You can use CHARINDEX to find the spaces, then UPPER the character following each space.|||How can I do this automatically in the Formula field of the Table Design.

thanks

How to make a enquiry for all database instances?

In SQl2000 i were abe to run an enquiry called "DATABASE $ALL" it was used to
enquiry all instance names on a SQL server for backup reasons, after
upgrading to SQL2005 this enquiry doesn't work.
Is there a new way to enquiry for all instance names in SQL2005?
Sr. System Engineer
Symantec
Hi Kennet
How are you running this enquiry? With T-SQL I would expect that you would
look at the sys.databases view. In SMO you have the databases member property
of the server object.
John
"Kennet Johansen" wrote:

> In SQl2000 i were abe to run an enquiry called "DATABASE $ALL" it was used to
> enquiry all instance names on a SQL server for backup reasons, after
> upgrading to SQL2005 this enquiry doesn't work.
> Is there a new way to enquiry for all instance names in SQL2005?
> --
> Sr. System Engineer
> Symantec

How to make a enquiry for all database instances?

In SQl2000 i were abe to run an enquiry called "DATABASE $ALL" it was used t
o
enquiry all instance names on a SQL server for backup reasons, after
upgrading to SQL2005 this enquiry doesn't work.
Is there a new way to enquiry for all instance names in SQL2005?
Sr. System Engineer
SymantecHi Kennet
How are you running this enquiry? With T-SQL I would expect that you would
look at the sys.databases view. In SMO you have the databases member propert
y
of the server object.
John
"Kennet Johansen" wrote:

> In SQl2000 i were abe to run an enquiry called "DATABASE $ALL" it was used
to
> enquiry all instance names on a SQL server for backup reasons, after
> upgrading to SQL2005 this enquiry doesn't work.
> Is there a new way to enquiry for all instance names in SQL2005?
> --
> Sr. System Engineer
> Symantec

How to make a enquiry for all database instances?

In SQl2000 i were abe to run an enquiry called "DATABASE $ALL" it was used to
enquiry all instance names on a SQL server for backup reasons, after
upgrading to SQL2005 this enquiry doesn't work.
Is there a new way to enquiry for all instance names in SQL2005?
--
Sr. System Engineer
SymantecHi Kennet
How are you running this enquiry? With T-SQL I would expect that you would
look at the sys.databases view. In SMO you have the databases member property
of the server object.
John
"Kennet Johansen" wrote:
> In SQl2000 i were abe to run an enquiry called "DATABASE $ALL" it was used to
> enquiry all instance names on a SQL server for backup reasons, after
> upgrading to SQL2005 this enquiry doesn't work.
> Is there a new way to enquiry for all instance names in SQL2005?
> --
> Sr. System Engineer
> Symantec

How to list the names of check constraint of a table and how to show it's content?

How to list the names of check constraint of a table?
How to show the content of a check constraint?
Thanks
--Frank, SQL2005This should give you a start:
SELECT * FROM sys.check_constraints
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Frank Lee" <Reply@.to.newsgroup> wrote in message news:O5k41PKKGHA.3944@.tk2msftngp13.phx.gb
l...
> How to list the names of check constraint of a table?
> How to show the content of a check constraint?
> Thanks
> --Frank, SQL2005
>|||Hi, Frank
This also works in SQL 2000 (as well as SQL 2005):
SELECT CC.CONSTRAINT_NAME, CC.CHECK_CLAUSE
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
INNER JOIN INFORMATION_SCHEMA.CHECK_CONSTRAINTS CC
ON TC.CONSTRAINT_NAME=CC.CONSTRAINT_NAME
WHERE TABLE_NAME='YourTable' AND CONSTRAINT_TYPE='CHECK'
Razvan