views:

102

answers:

2

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?

A: 

I do not believe this is possible. You cannot use a UDTT as the return type of a Scalar-Valued Function because it is not a scalar value. You also cannot replace the table declaration of a Table-Valued Function with a UDTT. Repeating the table definition seems to be the only option. If we knew why you were doing this, perhaps we could find an alternative.

Schmalls
I know i cant return a UDT from a Scalar function.But in regards to Table functions - you are able to return tables (or table variables). A UDT is just another table so cant see why you cant return it. Oh well, maybe in the next version of SS. =)Im doing this because i have a table variable which was being duplicated amongst numerous functions/procs. Therefore i put it in an UDT to prevent being "DRY". Easier to maintain, etc.
RPM1984
A: 

Ok - so it cant be done.

Easy enough to duplicate the table definition in the return type (with the use of scripting).

Still - hopefully this issue gets rectified in the next version of SQL Server.

RPM1984

related questions