Menu
I have my own type:
CREATE TYPE MyType AS TABLE
(
foo INT
)
and a function receiving it as a parameter:
CREATE FUNCTION Test
(
@in MyType READONLY
)
RETURNS @return MyType
AS
...
can it return MyType or only TABLE repeating MyType's structure:
CREATE FUNCTION Test
(
@in MyType READONLY
)
RETURNS @return TABLE (foo INT)
AS
...
?
632
For a multistatement scalar function, the function body can contain a series of Transact-SQL statements that return the single value. The return type can be any data type except text, ntext, image, cursor, and timestamp.
Once you connect to a database in SSMS, you can view these data types by navigating to Programmability-> Types->System Data Types.
Unlike tables, a table type cannot be altered by a simple command. I need to do the following: Rename existing table type. Create the new table type.
The user-defined table type definition cannot be modified after it is created. You will need to drop any procedures or functions which depend on the type; drop the type; create the type with the new definition; and finally re-create the dependant procedures / functions.
As far as I understand Microsoft's MSDN article here, those user-defined table types are only available as read-only parameters to stored procedures or stored functions.
It doesn't mention anything that they could be used to be returned from a user-defined function, unfortunately - so I guess you're right - it's not possible (at least not now).
193
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
