August 31, 2006 at 7:56 am
Hello,
I have a UDF Table :
Create Function getData(@id uniqueidentifier)
returns @retResults TABLE (of_id uniqueidentifier PRIMARY KEY, Value decimal(15,5))
AS
BEGIN
declare @val decimal(15,5)
set @val=(select fieldA from table1 where tabel_id=@id)
INSERT @retResults
SELECT @id, @ValRETURN
END
This function as you see returns only one line.
then i have a procedure where i try to do a select like this:
select a.table_id, b.Value
from tableA a
join getData(a.table_id) b where a.table_id=b.of_id
I get an error:
'table_id' is not a recognized OPTIMIZER LOCK HINTS option.
using SQL SERVER 2000
This code is a sample. It's not the one i'm using...
Any ideas?
August 31, 2006 at 8:53 am
Try to qualify your function with the owner, like this:
select a.table_id, b.Value
from tableA a
join dbo.getData(a.table_id) b where a.table_id=b.of_id
otherwhise the parser thinks getData is a table and (a.table_id) an optimizer hint.
Jan
August 31, 2006 at 8:58 am
Hi,
i have try it but that is not the problem...
I try to get the results to a temp teble and then do the join with that temp table.
It takes to long and i get deadlock after 5 minutes...
August 31, 2006 at 9:03 am
Why would you put everything in a temp table first and join that again afterwards? Everything record in your temporary table will have to be written to the tempdb (wether it is a qualifying record in the final result set or not). Then joining with the temp table will be joins without statistics, without indexes, etc...
I would suggest joining the tables directly without temp table.
Jan
November 24, 2006 at 7:32 am
Hi,
I am facing the same problem here. Can anyone tell me how to fix this?
Thank You,
Sérgio
November 24, 2006 at 8:07 pm
> Can anyone tell me how to fix this?
Upgrade to SQL Server 2005 and join the table-valued function using CROSS APPLY or OUTER APPLY.
You must pass a constant (or constant expression) to a table-valued function used in a FROM clause in SQL Server 2000.
-Eddie
Eddie Wuerch
MCM: SQL
November 24, 2006 at 8:25 pm
Create Function getData(@id uniqueidentifier)
returns @retResults TABLE (of_id uniqueidentifier, Value decimal(15,5))
AS
BEGIN
INSERT @retResults
(of_id, Value)
SELECT @id, fieldA
from table1
where tabel_id = @id
RETURN
END
I'm not sure why you need to pass original @id back to where it came from. Jusy to keep network occupied?
And if tabel_id is a PK in table1 then you gonna return single value all the time. That's a reason to make the function scalar:
Create Function getData(@id uniqueidentifier)
returns @Value decimal(15,5)
AS
BEGIN
SELECT @Value = fieldA
from table1
where tabel_id = @id
RETURN
END
If you change it to scalar your problem must been gone. Your problem is not in finction but in the way you are trying to use it.
_____________
Code for TallyGenerator
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply