Openquery for executing procedure from linked server with i/p and o/p parameters

Question

I need to execute a procedure from the linked server using an openquery()

create procedure test(@ip varchar(10),@op varchar(10) output)  
as  
begin  
if @ip='a'  
begin  
set @op='Success'  
end  
end

if suppose the procedure is like above and i need to return the o/p of @op variable by passing i/p as @ip variable

the SP is successfully executed with this code, but need to run using openquery().

declare @op varchar(10)
execute <servername>.<dbname>.dbo.test 'a',@op OUTPUT
select @op

how to pass o/p parameter to openquery select * from openquery (<servername>,'execute <dbname>.dbo.<sp_name>') //like this we can run a procedure who don't have any parameter

You have to run the query using `sp_executesql`. The following question and answer solve your problem. Take a look at the topic https://stackoverflow.com/questions/28327092/how-to-set-a-variable-using-openquey-in-sql-server — mohabbati, Nov 20 '18 at 05:48

score 0 · Answer 1 · answered Apr 24 '19 at 21:06

Create Procedure [dbo].[StoredProcedureName]     
     @paramOne varchar(13),
     @paramTwo varchar(2),
     @paramThree varchar(30)

As  
   Begin Try 

          DECLARE @param_in_1 VARCHAR(13);
          DECLARE @param_in_2 VARCHAR(2);
          DECLARE @param_in_3 VARCHAR(20);
          DECLARE @param_out_1 INT = 2;
          DECLARE @param_out_2 VARCHAR(300);

          SET @param_in_1 = @paramOne;
          SET @param_in_2 = @paramTwo;
          SET @param_in_3 = @paramThree;

          EXECUTE ('BEGIN OracleStoredProcedureName(?,?,?,?,?); END;', @param_in_1, @param_in_2, @param_in_3, @param_out_1 OUTPUT, @param_out_2 OUTPUT) AT LkServerName;              

          SELECT @param_out_1, @param_out_2;

   End Try
   Begin Catch

          DECLARE @ErrorNumber INT = ERROR_NUMBER();
          DECLARE @ErrorLine INT = ERROR_LINE();
          DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE();
          DECLARE @ErrorSeverity INT = ERROR_SEVERITY();
          DECLARE @ErrorState INT = ERROR_STATE();
          DECLARE @CompleteErrorMessage varchar(150);


          Print 'ErrorMessage: ' + @ErrorMessage;                
          Print 'Severity: ' + CAST(@ErrorSeverity AS VARCHAR(10));                  
          Print 'State: ' + CAST(@ErrorState AS VARCHAR(10));
          Print 'Error Number: ' + CAST(@ErrorNumber AS VARCHAR(10));
          Print 'Line: ' + CAST(@ErrorLine AS VARCHAR(10));      


   End Catch

Openquery for executing procedure from linked server with i/p and o/p parameters

1 Answers1