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