xDM

1. Create a procedure at the Semarchy Repository that wraps the existing GET_CONTINUOUS_LOADID stored procedure, the idea is to return the loadid as a result set at the end of the stored procedure.

create or alter   PROCEDURE GET_CONTINUOUS_LOADID_WRAPPED 
    @P_CONTINUOUS_LOAD_NAME varchar(max)
WITH EXECUTE AS OWNER
AS 
   BEGIN
      DECLARE

         @LOAD_ID numeric ;
         EXEC GET_CONTINUOUS_LOADID @LOAD_ID OUTPUT, @P_CONTINUOUS_LOAD_NAME

    SELECT @LOAD_ID;
    RETURN 
   END 

  
2. Create the scoped credentials and external data source

  

-- credential maps to a login or contained user used to connect to remote database 
CREATE DATABASE SCOPED CREDENTIAL semarchy_repository_cred -- credential name
WITH IDENTITY = 'semarchy_repository',                    -- login or contained user name
SECRET = 'yourpassword';                    -- login or contained user password

-- data source to remote Azure SQL Database server and database
CREATE EXTERNAL DATA SOURCE SemarchyRepository
WITH
(
    TYPE=RDBMS,                           -- data source type
    LOCATION='yourserver.database.windows.net', -- Azure SQL Database server name
    DATABASE_NAME='semarchy_repository',         -- database name
    CREDENTIAL=semarchy_repository_cred                -- credential used to connect to server / database  
);

  3. Then you can execute the new stored procedure using sp_execute_remote

EXEC sp_execute_remote @data_source_name = N'SemarchyRepository', 
@stmt = N'GET_CONTINUOUS_LOADID_WRAPPED @name',
@params = N'@name nvarchar(128)',
@name = N'CONT_LOAD_TEST'