T-SQL Stored Procedure Join Table Valued Parameter Twice

Question

I have a table valued parameter that I want to join, not just once, but twice. Once to get a row, and the second time to get the parent row.

I am almost certain this can be done with normal tables. Can it be done with table valued parameters?

USE [ActivityStore_220.0_Model]
GO
/****** Object:  StoredProcedure [dbo].[SnapshotLineItemAggregations]    Script Date: 3/24/2017 5:49:43 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SnapshotLineItemAggregations]    
    @billingCycleIteration int,
    @tableOfBusinessEntities [dbo].[BusinessEntityNode] READONLY,
    @tableOfActivityNames [dbo].[IdNamePair] READONLY
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    BEGIN TRANSACTION

    --Note this will ONLY delete items that have the exact same BillingCycleIterationId.
    --This will not delete old records from previous billing cycle iterations.  
    DELETE FROM [dbo].[LineItemAggregationSnapshot] WHERE 
    BillingCycleIterationId = @billingCycleIteration

    -- Insert statements for procedure here
    INSERT INTO [dbo].[LineItemAggregationSnapshot]
    SELECT [BillingCycleIterationId]
      ,[BillToAccountId]      
      ,CASE be_table.BusinessEntityTypeId WHEN 1 THEN 'Merchant' ELSE 'Organization' END AS [BusinessEntityType]
      ,[BusinessEntityId]
      ,[ProductId]
      ,[ActivityId]
      ,[RateProfileId]
      ,[Count]
      ,[Quantity]
      ,[ConversionRate]
      ,[OriginatingCurrency]
      ,[ConvertedQuantity]
      ,[QuantityUnits]
      ,[Rate]
      ,be_table.Name [BusinessEntityDescription]
      ,parent_be.Name [ParentBusinessEntityName]      
      ,activity_table.Name [ItemDescription]
      ,[LineItemData]
    FROM [dbo].[LineItemAggregation] lia
    LEFT OUTER JOIN @tableOfBusinessEntities be_table
    ON be_table.Id = lia.BusinessEntityId
    LEFT OUTER JOIN @tableOfActivityNames activity_table
    ON activity_table.Id = lia.ActivityId
    LEFT OUTER JOIN @tableOfBusinessEntities parent_be --the second join
    ON parent_be.Id = be_table.ParentId
    WHERE BillingCycleIterationId = @billingCycleIteration

    COMMIT TRANSACTION
    select * from LineItemAggregationSnapshot
    where BillingCycleIterationId = @billingCycleIteration
END

I had no problems with this stored proc, when I only joined @tableOfBusinessEntities once. But the second time I try to join the table valued parameter in, the stored procedure no longer seems to work.

I'm not getting an error, every column I'm trying to join is just showing null, as if the join rows didn't match any more.

Answer 1

So, the error was elsewhere in my code. How I was populating the .NET DataTable, or rather how I wasn't.

Once that problem was fixed, then the stored procedure worked like a charm.

So the answer for anyone wondering in the future is YES, you can join table valued parameters multiple times just as you would a normal table in SQL Server.