SQL inline function with array input with 2 columns

Question

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)
)

score 0 · Accepted Answer · answered Oct 26 '21 at 13:10

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 ....
);

Great, that's it, thank you very much – NoNa1961 Oct 28 '21 at 05:37 — NoNa1961, Oct 28 '21 at 05:37

SQL inline function with array input with 2 columns

1 Answers1