SQL inline function with array input with 2 columns-CodePudding

I have an inline function with an array input (dbo.Invoicenrs_table) like below, how can I extend this function to have a parameter more in the array input (I have extended the user defined table with an extra column) and the extra parameter must also be in the select.

FUNCTION  [dbo].[fn_SelectInvoices]
(
      @Invoice_Nrs dbo.Invoicenrs_table READONLY
)
RETURNS TABLE 

AS
RETURN
(      
      SELECT 
      [Invoice_Number] ,
      [Total_value]
    FROM Invoice WHERE [Invoice_Number] IN ( SELECT * from @Invoice_Nrs)
)

CodePudding user response：

If you are adding additional columns to your table type and you now need to match on multiple columns then you can just change the WHERE clause to an INNER JOIN to match any or all columns from the table parameter with your data table:

ALTER FUNCTION  [dbo].[fn_SelectInvoices]
(
      @Invoice_Nrs dbo.Invoicenrs_table READONLY
)
RETURNS TABLE 

AS
RETURN
(      
      SELECT 
      i.[Invoice_Number],
      i.[Col2],
      i.[Col3]
      i.[Total_value]
    FROM Invoice i
    INNER JOIN @Invoice_Nrs n ON i.[Invoice_Number] = n.[Invoice_Number
                             AND i.[Col2] = n.[Col2]
                             AND i.[Col3] = n.[Col3] --if you end up extending your table type further
                             AND ....
);