How to bring multiple output values from a stored procedure from another server using dynamic sql-CodePudding

I'm working on a stored procedure that needs to call a procedure that is in another server using dynamic SQL. But I need to get the two output values and a return value if some condition is met. This is an example code:

CREATE PROCEDURE [MYPROCEDURE](
@PARAM1 INT,
@PARAM2 NVARCHAR(250),
@PARAM3 INT,
@SERVER_NAME NVARCHAR(MAX))
AS
BEGIN
   DECLARE @SQLQUERY NVARCHAR(MAX)
   SET @SQLQUERY = 'EXEC '   @SERVER_NAME   '.[dbo].[DB].[OTHERPROCEDURE] @PARAM1,@PARAM2,@PARAM3'
   EXEC (@SQLQUERY)
END

This is how the "OTHERPROCEDURE" would look like

CREATE PROCEDURE [OTHERPROCEDURE](
@PARAM1 INT,
@PARAM2 NVARCHAR(250) OUTPUT,
@PARAM3 INT OUTPUT,
AS
BEGIN
   --DO SOMETHING
   IF SOME_CONDITION_IS_MET
   BEGIN
      --DO THIS
      RETURN SOME_VALUE
   END
   ELSE IF SOME_OTHER_CONDITION
   BEGIN
      --DO THIS
      RETURN SOME_VALUE
   END
   ELSE
   BEGIN
      --DO THIS, DON'T RETURN ANY VALUE
   END
--FINISH DOING SOMETHING WITH PARAMS
--DON'T RETURN ANY VALUE
END

Thanks in advance.

CodePudding user response：

This doesn't actually need dynamic SQL at all, as the procedure name for EXEC can be parameterized

CREATE PROCEDURE [MYPROCEDURE] @Param1 int,
                               @Param2 nvarchar(250) OUTPUT,
                               @Param3 int OUTPUT,
                               @ServerName sysname
AS

DECLARE @procName nvarchar(1100) = QUOTENAME(@ServerName)   N'.[dbo].[DB].[OTHERPROCEDURE]';

EXEC @Return = @procName
  @PARAM1 = @PARAM1,
  @PARAM2 = @PARAM2 OUTPUT,
  @PARAM3 = @PARAM3 OUTPUT;

RETURN @Return;

go

CodePudding user response：

Assuming by RETURN in the definition of OTHERPROCEDURE you do mean RETURN, then use a parametrised dynamic statement:

CREATE PROCEDURE [MYPROCEDURE] @Param1 int,
                               @Param2 nvarchar(250),
                               @Param3 int,
                               @ServerName sysname
AS
BEGIN
    DECLARE @SQL nvarchar(MAX);
    SET @SQL = (SELECT N'EXEC @Return = '   QUOTENAME(s.name)   N'.[dbo].[DB].[OTHERPROCEDURE] @PARAM1,@PARAM2,@PARAM3;'
                FROM sys.servers s
                WHERE s.[name] = @ServerName)
    DECLARE @Return int;
    EXEC sys.sp_executesql @SQL, N'@Param1 int, @Param2 nvarchar(250),@Param3 int,@Return int OUTPUT', @Param1, @Param2, @Param3, @Return OUTPUT;

    RETURN @Return;
END;