Dear Forum,

I hope someone knows the trick. In a rather complicated computation, I use a table-valued function with two Parameters to compute some data. Things run fine as long as you call the function for fixed Parameters, e.g.  select * from dbo.fn_myfunc(100, 200).

However, I need to run the function for all entries in a table, and this does not seem to work easily. Here's the example (simplified):

CREATE FUNCTION LargeOrderShippers ( @Freight_min money, @Freight_max money)
RETURNS @OrderShipperTab TABLE
( ShipperName   nvarchar(80),
  OrderID       int,
  Freight       money
)
AS
BEGIN
 INSERT @OrderShipperTab
   SELECT S.CompanyName, O.OrderID, O.Freight
     FROM Shippers AS S
    INNER JOIN Orders AS O ON (S.ShipperID = O.ShipVia)
    WHERE O.Freight between @Freight_min and @Freight_max
   RETURN
END

go
create table freight_manager(manager_name varchar(20), min_value money, max_value money)
go
insert freight_manager values ('Kathy', 100, 150)
insert freight_manager values ('Fred', 150, 200)
insert freight_manager values ('Rob', 200, 300)
insert freight_manager values ('Ken', 300, 700)

Now, how do you call the function for each table-entry ? A cross-join does not seem to work....

select * from freight_manager
cross join LargeOrderShippers ( freight_manager.min_value, freight_manager.max_value)

...thorws a sytnax error. Any ideas? I can't believe you need a cursor to step through the table

Regards,

Kay

For the first, CROSS JOIN creates a CARTESIAN PRODUCT, which means you end up with ALL combinations of freight_manager and LargeOrderShippers!

Alter your function to include something to bind freight_manager to shippers and order.

Then call with either

select     *
from       freight_manager
inner join dbo.LargeOrderShippers(min_value, max_value) AS los on los.someID = freight_manager.someID

Per Books Online (BOL):

"Only constants and @local_variables can be passed to table-valued functions."

"The value of each declared parameter must be supplied by the user when the function is executed."

This means that the parameters (@Freight_min money, @Freight_max money) can be a variable or a constant, but cannot be a per row value as the parameters are evaluated when executed.

Example:

SELECT * FROM LargeOrderShippers(500,1000)

DECLARE @Freight_min money, @Freight_max money
SELECT @Freight_min money = 500, @Freight_max money = 1000
SELECT * FROM LargeOrderShippers(@Freight_min, @Freight_max)

Andy