SQL Server Performance Forum – Threads Archive
About UDFs
Hi,Has anyone every experienced with user defined function ?
Do you know anybody, haw can I use table columns as input param
for my function ????????
I create table-valued function and work fine in this case: SELECT * FROM dbo.MyFunction(1,0) IdValue Value
———– ———–
1 OK or in this: SELECT Table1.IdValue, Table1.Name, Fn.Value
FROM Table1
JOIN dbo.fnMyFunction(1,0) ON Table1.IdValue = FnValue.IdValue IdValue Name Value
———– ———– ———–
1 JohnOK
but i cannot use it in case: SELECT Table1.IdValue, Table1.Name, Fn.Value
FROM Table1
JOIN dbo.fnMyFunction(Table1.IdValue,0) ON Table1.IdValue = FnValue.IdValue Server: Msg 170, Level 15, State 1, Line 3
Line : Incorrect syntax near ‘.’. OR SELECT Table1.IdValue, Table1.Name, Fn.Value
FROM Table1
JOIN dbo.fnMyFunction(IdValue,0) ON Table1.IdValue = FnValue.IdValue Server: Msg 155, Level 15, State 1, Line 2
‘IdValue’ is not a recognized OPTIMIZER LOCK HINTS option.
Thanks very much!
Osni S. Cavalcanti
SELECT Table1.IdValue, Table1.Name, f.Value
FROM Table1
JOIN dbo.fnMyFunction(Table1.IdValue,0) f ON Table1.IdValue = f.IdValue Bambola.
OK, I forgot the alias, but the error is the same: Server: Msg 170, Level 15, State 1, Line 3
Line : Incorrect syntax near ‘.’.
thanks, Osni S. Cavalcanti
The correct examples are SELECT * FROM dbo.MyFunction(1,0)
IdValue Value
———– ———–
1 OK & SELECT Table1.IdValue, Table1.Name, Fn.Value
FROM Table1
JOIN dbo.fnMyFunction(1,0) Fn ON Table1.IdValue = Fn.IdValue IdValue Name Value
———– ———– ———–
1 John OK
I cannot use it in these cases: SELECT Table1.IdValue, Table1.Name, Fn.Value
FROM Table1
JOIN dbo.fnMyFunction(Table1.IdValue,0) Fn ON Table1.IdValue = Fn.IdValue Server: Msg 170, Level 15, State 1, Line 3
Line : Incorrect syntax near ‘.’. OR SELECT Table1.IdValue, Table1.Name, Fn.Value
FROM Table1
JOIN dbo.fnMyFunction(IdValue,0) Fn ON Table1.IdValue = Fn.IdValue Server: Msg 155, Level 15, State 1, Line 2
‘IdValue’ is not a recognized OPTIMIZER LOCK HINTS option.
Osni S. Cavalcanti
I don’t think you can pass the column to the function… and I don’t exactly understand what are you trying to do. If you mean the function to work on the column you would not need the join SELECT Table1.IdValue, Table1.Name, dbo.fnMyFunction(IdValue,0)
FROM Table1 Otherwise, could you explain better what are you tying to do and maybe post the function code? Bambola.
Sometimes a better idea is to write your function so it performs on the whole table and returns a result set, rather than a scalar value.
You can then join the results of the function back onto the source table.
The benefit of this is it will help avoid costly row-by-row function iteration.
In the truth my function returns some fields.
ex.
SELECT Table1.IdValue, Table1.Name, Fn.Value, Fn.Value2, Fn.Value…, Fn.Value12
FROM Table1
JOIN dbo.fnMyFunction(Table1.IdValue,0) Fn ON Table1.IdValue = Fn.IdValue That they are calculated in set for the UDF Using scalar UDFs it would be: SELECT Table1.IdValue, Table1.Name,
dbo.fnMyFunction(Table1.IdValue,0).Value1,
dbo.fnMyFunction(Table1.IdValue,0).Value2,
dbo.fnMyFunction(Table1.IdValue,0).Value3,
dbo.fnMyFunction(Table1.IdValue,0).Value4,
dbo.fnMyFunction(Table1.IdValue,0).Value5,
dbo.fnMyFunction(Table1.IdValue,0).Value12,
FROM Table1
I would increase the processing (that already it is great) very much.
Sorry my bad english. thanks again,,, Osni S. Cavalcanti
As Bambola said, you can’t pass columns to a table-valued function I’d consider using computed columns to do this… You can add a computed column which calls a UDF, you can then index it to materialise the value (so that the UDF does not need to be called each time it is selected, only when the row is updated/inserted) Cheers
Twan
]]>