0

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
user1532976
  • 469
  • 2
  • 8
  • 15
  • If possible I would do the reverse. Put the logic in the function and call the function from the stored proc. – darin Mar 02 '13 at 04:52
  • You actually can call stored procedures from functions, from SQL Server 2005 to SQL Server 2008 R2, but in a very hacky and not recommended, but valid, way XD. As long as the stored procedure returns a single resultset. – Guillermo Gutiérrez Dec 26 '13 at 23:27

1 Answers1

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.

Community
  • 1
  • 1
David W
  • 10,062
  • 34
  • 60
  • David..This is failing..select ..,..,..,exec sp_GetCatalogNumber a.ProductId as CatalogNumber,.. from.. – user1532976 Mar 04 '13 at 19:02
  • You can't call the SP that way (as a field in a SELECT). What you can do is add an OUTPUT parameter to the SP, declare a corresponding variable in the calling SP, and call the SP to put the result in the output parameter. You can then include that variable in your SELECT. – David W Mar 04 '13 at 20:12