Unit Testing for Table-Valued Functions

Related products: Software Factory

As a developer, I aim to unit test my table-valued functions independently from the functionality where they are used. This approach provides better results and simplifies the setup of unit tests.

Unfortunately, the current documentation indicates that testing table-valued functions separately is not supported. However, I propose an alternative approach:

  1. Setup: Use the same setup as for a scalar function.
  2. Output Testing: Verify the function’s output using an assertion query.

Below is an example of how the generated code might look:

-- Declare parameters
declare @first_id int = convert(int, '1');
declare @second_id int = convert(int, '2');
declare @return_table table (
"id_1" "int" null,
"id_2" "int" null,
"value_1" "varchar(10)" null

-- Execute unit test
insert into @return_table (id_1, id_2, value_1)
select id_1, id_2, value_1
from dbo.table_valued_function(@first_id, @second_id);

-- Assertion query
if exists (select 1 from @return_table)
exec tsf_send_assertion_msg 1, 'Successful';
exec tsf_send_assertion_msg 0, 'Failed! No results returned!';

This solution leverages the UI input for the function and validates the results using the assertion query. The “table_valued_function” takes two input parameters (“first_id” and “second_id”) and returns a table named “return_table” with three columns: “id_1,” “id_2,” and “value_1.”


OpenWorking on it!