asked on

Joining to a User Defined Table-Valued Function

I am trying to join to a table valued function in SQL Server 2000. However I cannot pass a column value from a table as a parameter. But I can pass a hard value such as 14548 or even a variable set outside the select statement.

Does not work:
SELECT LDM.LDMID,
      LDM.OrigQORID,
      LDM.DestQORID
FROM dbo.RGLDM LDM
      INNER JOIN dbo.usf_ContainerRentals(LDM.LDMID) F ON LDM.OrigQORID = F.QORID
WHERE LDM.Active = 1
ORDER BY LDM.Sequence

Does Work
SELECT LDM.LDMID,
      LDM.OrigQORID,
      LDM.DestQORID
FROM dbo.RGLDM LDM
      INNER JOIN dbo.usf_ContainerRentals(14548) F ON LDM.OrigQORID = F.QORID
WHERE LDM.Active = 1
ORDER BY LDM.Sequence

Does Work:
DECLARE @MyID INT
SET @MyID = 14548
SELECT LDM.LDMID,
      LDM.OrigQORID,
      LDM.DestQORID
FROM dbo.RGLDM LDM
      INNER JOIN dbo.usf_ContainerRentals(@MyID) F ON LDM.OrigQORID = F.QORID

ASKER CERTIFIED SOLUTION

Brendt Hess

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Éric Moreau

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

dpbouchard

ASKER

emoreau, that is a great question. If this is available in 2005 that will solve the problem, for we are migrating next month. If anyone knows the answer to that and point me to where I can verify it, that would be great.

bhess1,
We have an identity "QORID" that is set in a global database. We have several other databases (on the same server) that reference this identity. I need to be able to join on a row in that particular database from the global. But with each record the QORID changes so the database that the record comes from also changes acording to what the QORID is. (BTW...We have a table that maps the QORID to the database name that it applies to.)