Calling SQL server store procedure with return value

Question

I'm trying to call Sql Server Store procedure that returns int value, not as output parameter. I've tried Oracle Sql Developer tool using following statements:

EXECUTE my_procedure 'TTT', '<a>abcd</a>';

EXEC  my_procedure 'TTT', '<a>abcd</a>';

It gives following error:

Error report:
Incorrect syntax near the keyword 'BEGIN'.

I've tried How to call a mysql stored procedure, with arguments, from command line?

It gives following error:

Error report:
Unknown Command

Following is my store procedure:

CREATE PROCEDURE my_procedure @advisor de , @adv_xml xml   

AS      


begin        
  declare 
    @psrg_idi idi,
    @adv_cd cd,
    @CurrDate cdt

  set @adv_cd = (select adv_cd from dbo.ADVICE_LK where upper(rtrim(adv_de)) = upper(@advisor))
  set @psrg_idi = 0    
  set @CurrDate = getdate()

    BEGIN TRY     
      exec my_proc_2 @CurrDate,@psrg_idi output

      insert into ADVICE (psrg_idi,adv_cd,psra_original_xml)  
      values
        (@psrg_idi,@adv_cd,@adv_xml)  

      select 
        @psrg_idi as psrg_idi

    END TRY     
    BEGIN CATCH    
       DECLARE @ErrorMessage NVARCHAR(4000);    
       DECLARE @ErrorSeverity INT;    
       DECLARE @ErrorState INT;     
       SELECT     
         @ErrorMessage = ERROR_MESSAGE(),    
    @ErrorSeverity = ERROR_SEVERITY(),    
    @ErrorState = ERROR_STATE();    
   RAISERROR (@ErrorMessage, -- Message text.    
     @ErrorSeverity, -- Severity.    
     @ErrorState -- State.    
     );    
   return -1    
     END CATCH    

end

Please answer with the with valid statements, to execute such procedure....

Is it working on sqlServer? because the error states that is a compilation problem. — Jorge Campos, Dec 03 '13 at 12:05
In Oracle you have to put the `DECLARE` block before the `BEGIN` statement. Not sure if this is valid for SQL Server as well. — Armunin, Dec 03 '13 at 12:07
How did you create the stored procedure? Your tool should have complained that it doesn't know what parameter `@advisor de` is. You really should use SQL Server tools when using SQL Server — Panagiotis Kanavos, Dec 03 '13 at 12:07
Yes, its working fine.. when calling from Java code then work perfect... I need to call it from command line tools or as sql statement.. — Imran, Dec 03 '13 at 12:07
What is the question then? How to execute SQL Server statements from inside Oracle's tools? — Panagiotis Kanavos, Dec 03 '13 at 12:08
@Panagiotis Kanavos: Oracle Sql Developer tool has support from sql server.. so why shouldn't we use it.. — Imran, Dec 03 '13 at 12:09
@Panagiotis Kanavos : Question is How to execute SQL Server store procedure ??? from any client tool e.g. SQL developer tool.. — Imran, Dec 03 '13 at 12:10
Because it may complain while trying to parse the statement as an Oracle statement. If you can run the statement from Java and SSMS, you should look to Oracle Dev for the problem. Is the line part of a larger script? The error about `BEGIN` means that either the stored procedure can't compile or that you have extra code you didn't post — Panagiotis Kanavos, Dec 03 '13 at 12:16
possible duplicate of [How to Execute SQL Server Stored Procedure in SQL Developer?](http://stackoverflow.com/questions/4136817/how-to-execute-sql-server-stored-procedure-in-sql-developer) — Panagiotis Kanavos, Dec 03 '13 at 12:17
No, it's not working... Giving me following error.. I've updated the question as well Error report: Unknown Command — Imran, Dec 03 '13 at 12:20
Not really related, but why bother catching an error, only to raise it again? — GarethD, Dec 03 '13 at 12:47

score 0 · Answer 1 · answered Dec 10 '13 at 11:34

Incorrect syntax near begin, this error is comed as you are passing parameters to your stored procedure.and when the stored procedure doesnt find the placeholders for those parameters it will throw such error. so add place holder to the parameters which you are passing to you are stored procedure.

Plus as it is sql server stored procedure which has its own syntax,data types,it expects to follow those syntax.in your declare part you have defined variables with data type idi. there is no such data type called idi.so heres the modified code.i have only put the parameters.for the declare part you have to put sql server recognised data types.kindly change your stored procedure syntax to that of sql server recognised. i have commented out exec my_proc_2 @CurrDate,@psrg_idi output.please check how you call sql server stored procedure having output parameters.

CREATE PROCEDURE my_procedure @advisor de , @adv_xml xml   
@placeholder1 varchar(20),
@placeholder2 xml
AS      


begin        
declare 
@psrg_idi as varchar(20),
@adv_cd as varchar(20),
@CurrDate as date

set @adv_cd = (select adv_cd from dbo.ADVICE_LK where upper(rtrim(adv_de)) = upper(@advisor))
set @psrg_idi = 0    
set @CurrDate = getdate()

BEGIN TRY     
  --exec my_proc_2 @CurrDate,@psrg_idi output

  insert into ADVICE (psrg_idi,adv_cd,psra_original_xml)  
  values
    (@psrg_idi,@adv_cd,@adv_xml)  

  select 
    @psrg_idi as psrg_idi

END TRY     
BEGIN CATCH    
   DECLARE @ErrorMessage NVARCHAR(4000);    
   DECLARE @ErrorSeverity INT;    
   DECLARE @ErrorState INT;     
   SELECT     
     @ErrorMessage = ERROR_MESSAGE(),    
     @ErrorSeverity = ERROR_SEVERITY(),    
     @ErrorState = ERROR_STATE();    
   RAISERROR (@ErrorMessage, -- Message text.    
   @ErrorSeverity, -- Severity.    
   @ErrorState -- State.    
   );    
   return -1    
   END CATCH    

   end

Calling SQL server store procedure with return value

1 Answers1