Business Intelligence

SQL – Joining a Table with a Table Value Function Using Column Values as Parameters

In this example, I am attempting to join a SQL Table with a Table-valued Function that accepts column values from the SQL Table as parameters.

DECLARE @AllValues INT = 1, @ReportDate DATETIME = ‘4/1/2015’

SELECT
    [s].[ServiceId]
 
, [s].[ServiceName]  , [ro].[AvailableRollover]  , [ro].[RolloverUsed]  , [ro].[RolloverAdded]  , [ro].[RolloverExpired]  , [ro].[RolloverBalance]FROM
 
[dbo].[Service][s]  , [dbo].[fnRetrieveManagedServicesRolloverTotals](@AllValues, [s].[ServiceId], @ReportDate)[ro]

When I try to execute this query, I get the following error message:

The multi-part identifier “s.ServiceId” could not be bound.

You can resolve this issue by using CROSS APPLY:

SELECT
    [s].[ServiceId]
 
, [s].[ServiceName]  , [ro].[AvailableRollover]  , [ro].[RolloverUsed]  , [ro].[RolloverAdded]  , [ro].[RolloverExpired]  , [ro].[RolloverBalance]FROM
 
[dbo].[Service][s]  CROSS APPLY [dbo].[fnRetrieveManagedServicesRolloverTotals](@AllValues, [s].[ServiceId], @ReportDate)[ro]

Leave a Reply

Privacy Settings
Name Enabled
Cookies
We use Cookies to give you a better website experience.
x

We use cookies to ensure the best possible experience on our website. Detailed information on the use of cookies on this site is provided in our Privacy and Cookie Policy. You can also manage your preferences there.