How to insert multiple values to User_defined table types within a function

Question

------------------CREATING THE UDDT--------------------------------------
Create type audit.table_list AS TABLE
(
     table_name nvarchar(50),     
)
-----------------CREATING THE FUNCTION-----------------------------------    
CREATE OR ALTER FUNCTION [dbo].[test1] (@list audit.table_list readonly) 
    returns nvarchar(255) 
    
    as
    Begin
    DECLARE @name varchar(50)
    select  @name = table_name from @list
    return @name
    end 
-----------------------------------------------------------
----SELECTING THE RETURNED FUNCTION------------------------
    DECLARE @audit audit.table_list
    INSERT INTO @audit(table_name) VALUES('aa'),('bb')
    SELECT dbo.test1(@audit)
---Alternative I tried which was not able to return----
CREATE FUNCTION [dbo].[test] (@list audit.table_list readonly) 
returns @table_list table (table_name nvarchar(255)) 
as
Begin
DECLARE @Modifieduddt audit.table_list;
INSERT INTO @Modifieduddt(table_name) values('aaa');
return @modifiedUddt
end

This is inserting only one row/record and replacing the previous value. When I am trying to pass parameter within the function as a table instead of varchar(50) it is not letting me return the parameter which referring to the UDDT. I am trying to insert multiple values to the column in the UDDT. My goal is to pass table_name as column but when I do that I can't return @name within function

Answer 1

You pass in a table, and the SELECT in your UDF returns a result set which you then try to assign to @name which is NVARCHAR(255), and your UDF returns a NVARCHAR(255) result. In other words, you're trying to assign a result set to a NVARCHAR(255) variable which won't work.

If you want to return all of the results then your function should return a table instead of an NVARCHAR(255).

--Returns all values from @list
CREATE OR ALTER FUNCTION [dbo].[test1] (@list audit.table_list readonly) 
    returns TABLE

    as
    RETURN (SELECT table_name FROM @list)

--Use the UDF
SELECT * FROM [dbo].[test1];

If you want only a single result from the UDF then leave the return type as NVARCHAR(255) and modify the query to add a WHERE clause, or a TOP, or something that will result in a single value that can be stored in a NVARCHAR(255) variable.

--Returns a single NVARCHAR(255) value from @list
CREATE OR ALTER FUNCTION [dbo].[test1] (@list audit.table_list readonly) 
    returns nvarchar(255) 
    
    as
    Begin
    DECLARE @name varchar(50)
    select TOP 1 @name = table_name from @list
    return @name
    end

--Use the UDF
SELECT [dbo].[test1];

Note the difference in how the function is called between the two. The first makes the UDF a table-valued function (TVF), the second makes the function a scalar-valued function (SVF). With a TVF you use the function as you would a table (basically, there are other considerations) and with a TVF you use the function as a scalar value.