Here's my user-defined table type...
CREATE TYPE [dbo].[FooType] AS TABLE(
[Bar] [INT],
)
This is what ive had to do in my table-valued function to return the type:
CREATE FUNCTION [dbo].[GetFoos]
RETURN @FooTypes TABLE ([Bar] [INT])
INSERT INTO @FooTypes (1)
RETURN
Basically, im having to re-declare my type definition in the RETURN statement of the function. Isnt there a way i can simply declare the type in the RETURN statement?
I would have thought this would work:
CREATE FUNCTION [dbo].[GetFoos]
RETURN @FooTypes [FooType]
INSERT INTO @FooTypes (1)
RETURN
Cannot find any help on MSDN/Google regarding this....anyone?
EDIT
I unmarked my answer, and bumping this question - as i am encountering the same scenario 6 months later.
Does anyone have any idea if it's possible to return a user defined table type from a table valued function? If not, is there a better workaround other than what i have done? (re-declare the type again).
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.