Returning a User-Defined Table Type from a Multi-Statement Table-Valued Function
-
More of a curiosity question than an actual problem.
I've have a User-Defined Table Type:
CREATE TYPE [dbo].[IDTable] AS TABLE(
[ID] [int] NOT NULL,
PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF)
)
GO
I created a Multi-statement Table-valued Function (simplified for the example):
CREATE FUNCTION dbo.GetProductIDs
(
-- Add the parameters for the function here
@flag int
)
RETURNS
@ProductIDTbl dbo.IDTable <------------------------------------------------------Part that Server 2008 does not like.
AS
BEGIN
INSERT INTO @ProductIDTbl (ID)
SELECT pt. ProductID
FROM dbo.ProductTable pt
WHERE pt.SomeFlag = @flag
RETURN
END
GO
Why can't I use a pre-defined table type as the return from the function?
-
djshireman (1/23/2012)
More of a curiosity question than an actual problem.Why can't I use a pre-defined table type as the return from the function?
Currently, user-defined table types are for passing data into procedures and functions only, and they require the READONLY attribute to do that. A function or procedure cannot modify the contents (hence READONLY) so unless you wanted to return the same table you received as a parameter, there wouldn't be much point supporting this here. Hopefully, a future version of SQL Server (not 2012 RTM) will enhance the abilities of table-valued parameters and variables in general.
-
Interesting, I think I'm just trying to inappropriately apply programming techniques to my SQL experiments.
Thanks for the info.
-
djshireman (1/24/2012)
Interesting, I think I'm just trying to inappropriately apply programming techniques to my SQL experiments.Oh I wouldn't say that - perhaps it's just T-SQL hasn't got that far yet
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply