'Cannot return a user table type form a function

I just wrote this coe example to return a user type from a function:

CREATE TYPE dbo.ScheduledActivity_TVP AS TABLE 
(
    Id uniqueidentifier  NOT NULL primary key, 
    AdditionalDataTypeSignature nvarchar(100) not null, 
    AdditionalDataId uniqueidentifier NOT NULL,
    AdmissionId uniqueidentifier NOT NULL
)
GO

CREATE  OR ALTER function [dbo].[Fun_GetFollowUpBymonth](@admissionId uniqueidentifier)      
            returns ScheduledActivity_TVP  as 
            begin
            declare @q ScheduledActivity_TVP
            insert into @q 
            select Id,
                   AdditionalDataTypeSignature,
                   AdditionalDataId,
                   AdmissionId 
            from ScheduledActivities
            where @admissionId = ScheduledActivities.AdmissionId;

            return @q
GO

And Sql Server tells me that I must declare the scalar variable @q. What is wrong in the code above?

sqlsql-serversql-functionusertype


Solution 1:[1]

I don't see why you are using a multi-line table value function here; they are notoriously slow.

Use an inline table value function, which doesn't even need a TYPE:

CREATE OR ALTER FUNCTION [dbo].[Fun_GetFollowUpBymonth] (@admissionId uniqueidentifier)
RETURNS table
AS RETURN
    SELECT Id,
           AdditionalDataTypeSignature,
           AdditionalDataId,
           AdmissionId
    FROM dbo.ScheduledActivities
    WHERE @admissionId = ScheduledActivities.AdmissionId;

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Larnu

Related Questions