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

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. Further instruction on how to disable our cookies can be found there.