SQL Server SP_EXECUTE_REMOTE – Nested Dynamic Query Parameter Binding

azure-sql-databasesql server

I'm having two Azure Databases Lets say DB1(primary) and DB2(secondary).
I've Created two External Data sources for both the DB1 so that they can talk to each other

External Data Source for DB1 is External_DB2
External Data Source for DB2 is External_DB1

I'm having a Sp in my DB2.Lets say the sp is sp_DB2(@param1)
Like that another sp in my DB1.Say sp_DB1(@param1,@outparam)
(Ps:@outparam is a output parameter)

My sp_DB2 is the Main Sp (which calls all other Sp i.e, sp_DB1).

Inside my sp_DB2 i have the code like 

EXEC SP_EXECUTE_REMOTE
N'External_DB1',
N' DECLARE @param1 nvarchar(max),@outparam nvarchar(max)
EXEC SP_EXECUTESQL N''EXEC DBO.[sp_DB1]@param1,@outparam OUTPUT'',
N''@param1 nvarchar(max),@outparam nvarchar(max) OUTPUT'',
@param1 ,@outparam OUTPUT
SELECT @param1 AS Paramvalue,@outparam AS outputvalue'

Since sp_Execute_remote doesn't support the output param.I done like the above.But here the thing i want to bind the @param1 value from the Original Sp (sp_DB2).

If i tried like this it works,

EXEC SP_EXECUTE_REMOTE
    N'External_DB1',
    N' DECLARE @param1 nvarchar(max)=''Testing'',@outparam nvarchar(max)
    EXEC SP_EXECUTESQL N''EXEC DBO.[sp_DB1]@param1,@outparam OUTPUT'',
    N''@param1 nvarchar(max),@outparam nvarchar(max) OUTPUT'',
    @param1 ,@outparam OUTPUT
    SELECT @param1 AS Paramvalue,@outparam AS outputvalue'

Where i hardcoded the Value of @Param1 as Testing in the Code.

But actually the value of @Param1 should come from the original Sp which is sp_DB2

When i call the Original Sp like sp_DB2(Testing) It should bind with the @Param1 for the other Sp which is sp_DB1

By doing this we can reduce the Hardcoded value of Testing inside the Sp

Pls somebody help me to solve this

Thanks in Advance !
Jay

Best Answer

SP_EXECUTE_REMOTE in Azure works very similar to native sp_executesql. So you should be able to bind the params & values. But I believe there is a limitation that you can't get a output param value from it. Try this and see if it works for your case. Thanks.

EXEC SP_EXECUTE_REMOTE
    N'External_DB1',
    N' DECLARE @param1 nvarchar(max)= @param1_from_sp_DB2, @outparam nvarchar(max)
    EXEC SP_EXECUTESQL N''EXEC DBO.[sp_DB1]@param1,@outparam OUTPUT'',
    N''@param1 nvarchar(max),@outparam nvarchar(max) OUTPUT'',
    @param1 ,@outparam OUTPUT
    SELECT @param1 AS Paramvalue,@outparam AS outputvalue', 
    N'@param1_from_sp_DB2 nvarchar(max)', 
    @param1 --Since this is the input param name from sp_DB2
Related Question