Blog Pages

Cannot perform alter on a function because it is an incompatible object type

If you have a Multi-Statement Table-Valued Function (MSTVF):

CREATE FUNCTION [DataAccess].[ufn_GetLastPoliciesByTZ]
( ... )
RETURNS @ret TABLE (ID INT)
AS
BEGIN
...
RETURN
END


If you will try to alter it to – with changing it to an Inline Table-Valued Function:

ALTER FUNCTION [DataAccess].[ufn_GetLastPoliciesByTZ]
(
       @ClientPolTable [DataAccess].[TT_ClientPoliciesByTZ] READONLY,
       @SugMimshak INT,
       @FromNechonutDate DATETIME = NULL, -- In case no date is selected
       @ToNechonutDate DATETIME = NULL
)
RETURNS TABLE AS
RETURN
(  
       SELECT .....
)


You will be got this error:
Cannot perform alter on 'DataAccess.ufn_GetLastPoliciesByTZ' because it is an incompatible object type.

Solution:
You can't alter a function from MSTVF to Inline. So, drop and create again the function.
CREATE FUNCTION [DataAccess].[ufn_GetLastPoliciesByTZ]() RETURNS TABLE AS RETURN (SELECT 1 AS ID)

No comments:

Post a Comment