October 13, 2004 at 9:21 pm
i have a table returning UDF, it accepts a parameter say customer_no
something like this fnMyTABLEUDF(@customer_no int)
I can easily return a table (like a view with parameters)
with this query
select * from fnMYTABLEUDF(1234)
no sweat..
now i need to know if i can make use of this function to pass in the values from another table via a join statement perhaps?
something like this (it won't work)
example myCustomerTable = create table (customer_no int)
select * from myCustomerTable, fnMYTABLEUDF(customer_no)
or soemthing to that extent... the UDF getting values from a table?
Thanks.
October 14, 2004 at 8:03 am
From my understanding and to the best I can think of. You cannot do this and there is no way to work around it and still use the UDF.
October 14, 2004 at 7:05 pm
ok. Thanks for the reply.. i was thinking of using an UDF like a shortcut.. at the end i took the stored procedure approach, more line of codes.
Hopefully in future version i can do that.. 2005 can't do that too right.
October 14, 2004 at 9:49 pm
You can join to a UDF that returns a table. You might need to adjust your UDF to suit - I assume that the parameter for this is currently the customerid. Have a look at the following - it might be something like what you are after
create table myCustomerTable (custid int, custname varchar (100))
go
Insert into myCustomerTable values (1, 'Cust 1')
Insert into myCustomerTable values (2, 'Cust 2')
go
create function fnMYTABLEUDF ()--(@vintCustid Int)
Returns @Lookup Table (CustID Int, CustDate smalldatetime)
As
Begin
-- this pretends to be whatever logic is in your UDF
Insert Into @Lookup Values (1, '1 Jan 2004')
Insert Into @Lookup Values (2, '25 Jun 2004')
return
ENd
go
select * from myCustomerTable
Select * from myCustomerTable
Inner Join fnMYTABLEUDF () x
On myCustomerTable.custid = x.custid
Where x.custdate > '1 jan 2003'
Select * from myCustomerTable
Inner Join fnMYTABLEUDF () x
On myCustomerTable.custid = x.custid
Where x.custdate > '1 jan 2004'
October 15, 2004 at 1:46 am
hi happycat..
thanks for the reply.. yeah it works perfectly as what i mentioned in the first post without a parameter..
however currently i can't afford to without.. it will return more than a million records.. and certainly don't wish to hold it up on a table variable...
October 16, 2004 at 11:03 am
If you want to call a UDF with a parameter whose value is taken directly from a query (that may return more than one row), I'm afraid that it's not possible in SQL Server 2000. This will be possible in SQL Server 2005 (Yukon), using the APPLY operator. See the following page for details:
http://msdn.microsoft.com/library/en-us/dnsql90/html/sql_ovyukondev.asp?frame=true#sql_ovyuko_topic5
Razvan
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply