Remote function calls not allowed within a function

Question

Scalar values functions fails after calling remote function. Is there any way to call remote server function ?

This is the code

ALTER FUNCTION [dbo].[fnGetCatalogNumber] 
(
    -- Add the parameters for the function here
    @ProductID int
)
RETURNS varchar(20) 
AS
BEGIN
     -- Declare the return variable here
      DECLARE @CatalogNumber varchar(20), @catalog_data varchar(20)


    -- Add the T-SQL statements to compute the return value here
    --Exec Dev01.Product_Core.dbo.usp_get_CatalogNumber @ProductId = @ProductID 
     ,@CatalogNumber = @catalog_data output

    -- Return the result of the function
    RETURN @CatalogNumber

END

Answer 1

You cannot execute stored procedures within functions, because the assumption is that such a procedure might induce side-effects, such as table alteration.

You can contemplate recasting your function as a stored procedure, thereby allowing the internal stored procedure call, but be aware that doing so is fraught with danger, warning, and peril as discussed at length here.