SQL Server 2008 – How do i return a User-Defined Table Type from a Table-Valued Function?

by

Even though you can not return the UDTT from a function, you can return a table variable and receive it in a UDTT as long as the schema match. The following code is tested in SQL Server 2008 R2

— Create the UDTT

CREATE TYPE dbo.MyCustomUDDT AS TABLE
(
    FieldOne varchar (512),
    FieldTwo varchar(1024)
)

— Declare your variables

DECLARE @uddt MyCustomUDDT;
DECLARE @Modifieduddt MyCustomUDDT;

// Call the function

INSERT INTO @Modifieduddt SELECT * FROM dbo.MyUDF(@uddt);

Function signature

CREATE FUNCTION dbo.MyUDF(@localUDDT MyCustomUDDT)
RETURNS @tableVar TABLE
(
    FieldOne varchar (512),
    FieldTwo varchar(1024)
)
AS
BEGIN
 --Modify your variable here
RETURN
END

Hopefully this will help somebody.

More Related Contents:

  1. Comma separated results in SQL
  2. How to check if a column exists in a SQL Server table
  3. Understanding PIVOT function in T-SQL
  4. Using T-SQL, return nth delimited element from a string
  5. Get first day of week in SQL Server
  6. Using RegEx in SQL Server
  7. Multi-statement Table Valued Function vs Inline Table Valued Function
  8. How do you view ALL text from an ntext or nvarchar(max) in SSMS?
  9. Confused about UPDLOCK, HOLDLOCK
  10. INSERT INTO vs SELECT INTO
  11. SQL Server 2008 Empty String vs. Space
  12. How do I use the CONCAT function in SQL Server 2008 R2?
  13. How to set a default value for an existing column
  14. Select / Insert version of an Upsert: is there a design pattern for high concurrency?
  15. TSQL: How to convert local time to UTC? (SQL Server 2008)
  16. SQL Server: combining multiple rows into one row
  17. How do I find a stored procedure containing ?
  18. SQL Recursive CTE: Finding objects linked by property
  19. How to convert number of minutes to hh:mm format in TSQL?
  20. Convert decimal time to hours and minutes
  21. how to write number to word function in sql server [closed]
  22. How do I move a table into a schema in T-SQL
  23. Cannot find either column “dbo” or the user-defined function or aggregate “dbo.Splitfn”, or the name is ambiguous
  24. Instead of trigger in SQL Server loses SCOPE_IDENTITY?
  25. How Can I Detect and Bound Changes Between Row Values in a SQL Table?
  26. Does query plan optimizer works well with joined/filtered table-valued functions?
  27. How to wait for 2 seconds?
  28. What are row, page and table locks? And when they are acquired?
  29. Generate MD5 hash string with T-SQL
  30. Understanding SQL Server LOCKS on SELECT queries

Leave a Comment