table function blues! please help!!!!!!

[chenthorn]

I have a foxpro .dll that I use via extended stored procedure to scrub addresses in a number of tables. I need to convert this dll to sql so as to improve performance. My first stab was to create a "Multi statement table function".
I had hoped that I could select each row from my db table into the function as one parameter per column (i.e. select Scrubadd( housenum, street, city, state, zip, ...) from <tablename>).
I had wanted the function to then scrub all rows in my db table and return the corrected output to a table variable inside the function that i could select out of.
Many of my scrub statements move data from one column to another and blank out other columns.
Here is a snippet of my function code. I left ut a bunch for brevity:
CREATE FUNCTION fn_FixStreets ( @uid int,
@lname varchar(30),
@fname varchar(20),
@mname varchar(20),
@dob varchar(8),
@housenum varchar(15),
@streetdir varchar(2),
@street varchar(38),
@unit varchar(20),
@city Varchar(30),
@zip char(5),
@areacode varchar(3),
@phone varchar(7),
@pubdate varchar(8),
@verdate varchar(8),
@adddate varchar(8),
@histdate varchar(8),
@moddate varchar(8),
@Actflag char(1),
@tbl varchar(10) )

RETURNS @fixstreets TABLE ( UID int,
lname varchar(30),
fname varchar(20),
mname varchar(20),
dob varchar(8),
housenum varchar(15),
streetdir varchar(2),
street varchar(38),
unit varchar(20),
city Varchar(30),
zip char(5),
areacode varchar(3),
phone varchar(7),
pubdate varchar(8),
verdate varchar(8),
adddate varchar(8),
histdate varchar(8),
moddate varchar(8),
Actflag char(1) )
AS
BEGIN

/*********************************************
** Clean Addresses section
*********************************************/

IF @Street = ''
BEGIN
GOTO CleanNames
END

--trim spaces from street
SELECT @street = LTRIM(RTRIM(@Street))
SELECT @street = LTRIM(RTRIM(@housenum))
SELECT @street = LTRIM(RTRIM(@streetdir))
SELECT @street = LTRIM(RTRIM(@unit))
SELECT @street = LTRIM(RTRIM(@city))

(...)
IF ISNUMERIC(@Phone) <> 1
BEGIN
IF DBO.fnOccurs(@phone, 'X') >3
BEGIN
SELECT @phone = ''
END
END
SELECT @Phone = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@Phone, '[', ''), ']', ''), '{', ''), '}', ''), '!', ''), '@', ''), '#', ''), '$', ''), '^', ''), '&', '')
SELECT @Phone = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@Phone, '*', ''), '(', ''), ')', ''), '/', ''), '\', ''), '+', ''), '?', ''), '<', ''), '>', ''), '=', '')
SELECT @Phone = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@Phone, '-', ''), '%', ''), '|', ''), '&quot;', ''), ':', ''), ';', ''), '`', ''), '~', ''), '_', ''), '.', '')

SUCCESS:
-- copy the result of the function the required columns
INSERT @fixstreets
SELECT @uid,
@lname,
@fname,
@mname,
@dob,
@housenum,
@streetdir,
@street,
@unit,
@city,
@zip,
@areacode,
@phone,
@pubdate,
@verdate,
@adddate,
@histdate,
@moddate,
@Actflag

RETURN
END
GO

when i try to use the function like this:
SELECT dbo.fn_FixStreets(UID,
lname,
fname,
mname,
dob,
housenum,
streetdir,
street,
unit,
city,
zip,
areacode,
phone,
pubdate,
verdate,
adddate,
histdate,
moddate,
Actflag )
FROM NV_AMA

I get this error:
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'dbo.fn_FixStreets'.

when i try to use call it this way:
select * from fn_FixStreets(UID,
lname,
fname,
mname,
dob,
housenum,
streetdir,
street,
unit,
city,
zip,
areacode,
phone,
pubdate,
verdate,
adddate,
histdate,
moddate,
Actflag )
I get this error:
Server: Msg 155, Level 15, State 1, Line 1
'UID' is not a recognized OPTIMIZER LOCK HINTS option.

I am obviously missing something, can someone, anyone tell me what it is?
thanks!!!!!!!
Carl

 

[ClayG]

In you second attempt to call the function, the table that the columns come from is not named anywhere, thats why SQL doesnt understand &quot;UID&quot; (or the rest of the columns).

In theory, what you are trying to do is to apply a function to n columns and get n colums back, one row at a time. So this is really a row (tuple) function not a scalar function and not really a table-valued function. As far as a row is a one-row table you could imagine doing this:

Select tvf.*
from NV_AMA as N
inner join fn_FixStreets( n.UID, n.lname, n.fname, ..)

But unfortunately this does Not work either. Table-valued functions cannot be passed columns (I was disappointed too).
Basically, table functions work exactly as if they create a temp table before the query that calls them, and the query joins to that invisible temp table. So your table function will have to access the source table NV_AMA and return all the rows. Parameters are only good for filter conditions, and must be passed from variables.

The only other way is to make your functions scalar - returning one field - and have a parameter that identifies which field should be returned:

Select
fn_FixStreets( n.UID, n.lname, n.fname, .. 'lname') as lname,
fn_FixStreets( n.UID, n.lname, n.fname, .. 'fname') as fname, ..
from NV_AMA as N