Return value from dynamic linked server stored procedure

I want to return a value from a dynamic linked server stored procedure.

--storedprocedure on the remote linked server
ALTER PROCEDURE [dbo].[SPTEST]
@DocNumOut VARCHAR(20) output
AS
BEGIN
    SET NOCOUNT ON;
    SELECT @DocNumOut=1;
    RETURN @DocNumOut
END

On the local server, how can I retrieve the value of @DocNumOut? I have trial many combination using EXEC, openquery, EXEC sp_executesql, but none can return the value into my local variable, @DocNumberOUT.

--local server
DECLARE @DocNumberOUT as int;
DECLARE @SQLCMD AS VARCHAR(2000)='EXEC LinkedServer.MyDB.dbo.SPTEST'
EXEC @DocNumberOUT= sp_executesql @SQLCMD
--Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.

Answer

Use OUTPUT parameter on your Stored Procedure and don’t use the return value.

Use the proper data type. Don’t use varchar for everything. Since you assigned an integer to the variable, declare it as integer.

ALTER PROCEDURE [dbo].[SPTEST]
    @DocNumOut INT output
AS
BEGIN
    SET NOCOUNT ON;
    SELECT @DocNumOut=1;
END

On the calling side, the error message is telling you that @SQLCMD should declare as NVARCHAR. Using sp_executesql is the right choice. But you should also define the parameters and pass in the variable also. @DocNumOut is the name of the parameter that you defined in SPTEST. @DocNumberOUT is your local variable

DECLARE @DocNumberOUT as int;

DECLARE @SQLCMD AS NVARCHAR(2000);

SET @SQLCMD = 'EXEC [LinkedServer].[MyDB].[dbo].[SPTEST] @DocNumOut = @DocNumberOUT OUTPUT'

EXEC sp_executesql @SQLCMD, N'@DocNumberOUT INT OUTPUT', @DocNumberOUT OUTPUT

SELECT @DocNumberOUT