Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Joining to a User Defined Table-Valued Function

Posted on 2007-03-22
4
Medium Priority
?
277 Views
Last Modified: 2007-04-16
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
0
Comment
Question by:dpbouchard
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 32

Accepted Solution

by:
Brendt Hess earned 1600 total points
ID: 18775733
And that is correct.  When you execute the UDF to generate the table for the JOIN, this is done prior to the row-by-row  joining, so that the UDF is executed just once.  Since it only runs once, telling it to run based on the value in each row is nonsensical.

If you need it to run once for each row.... well, I think you need to be returning something other than a table.

So, please define what you are attempting to do in a bit more detail, and we'll see if we can help.

0
 
LVL 70

Assisted Solution

by:Éric Moreau
Éric Moreau earned 400 total points
ID: 18776478
this is a new feature in SQL 2005 (the cross apply operator).
0
 
LVL 1

Author Comment

by:dpbouchard
ID: 18781861
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.)
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

610 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question