cancel
Showing results for 
Search instead for 
Did you mean: 
brightlight
Nintex Newbie

Execute SQL - Stored Procedure with Variables as Parameters

Jump to solution

I am trying to run a stored procedure in a execute SQL action. My connection string is correct as i have been using to insert into tables for a while.

All parameters take int as my vaiables are also of int type. Below is the information:

 

SELECT [dbo].[ my_storedProcedureName]

 

@Parameter1={WorkflowVariable:intOne},

@Parameter2={WorkflowVariable:intTwo},

@Parameter3={WorkflowVariable:intThree}

 

I must be missing something.

 

Labels: (1)
Tags (1)
0 Kudos
Reply
4 Replies
brightlight
Nintex Newbie

Re: Execute SQL - Stored Procedure with Variables as Parameters

Jump to solution

Found it myself, it was quite simple than what i thought.

EXEC  my_storedProcedureName {WorkflowVariable:intOne}, {WorkflowVariable:intTwo},{WorkflowVariable:intThree}

 

Note: If you have output parameters, you have to include parameter and the word output:

EXEC  my_storedProcedureName {WorkflowVariable:intOne}, {WorkflowVariable:intTwo},{WorkflowVariable:intThree}, {WorkflowVariable:mltOutputParameter} output

0 Kudos
Reply
rodgonzalez
Nintex Newbie

Re: Execute SQL - Stored Procedure with Variables as Parameters

Jump to solution

Hi I did not understand how you applied the solution, can you please expand a little more.

I am trying to send data from a nintex form and executing a stored procedure from a nintex workflow action, but I have two responses that the SP must send me and I cannot capture them, as output parameters.

el SP:

PROCEDURE sp_insert_candidatos_man(p_employee IN varchar2,
p_periodo IN varchar2,
p_usuario IN varchar2,
p_fecha IN varchar2,
p_respuesta OUT number,
p_mensaje OUT varchar2);

 

La acción dentro de nintex workflow:

declare
v_respuesta NUMBER;
v_mensaje VARCHAR2(100);
begin
-- Call the procedure
spo_beneficios_pkg_rm5.sp_insert_candidatos_man(
p_employee => '381552',
p_periodo => '201909',
p_usuario => 'i:0#.w|******\rodgonzalez',
p_fecha => '2019-10-09 00:00:00',
p_respuesta => v_respuesta output,
p_mensaje => v_mensaje output);
end;

 

output.jpg

 

 

Gracias de antemano

 

0 Kudos
Reply
brightlight
Nintex Newbie

Re: Execute SQL - Stored Procedure with Variables as Parameters

Jump to solution

@rodgonzalez 

Make sure you follow the exact process when you write your query on your nintex workflow execute SQL action. Also, this is for SQL as i am not sure about other databases.

EXEC  my_storedProcedureName {WorkflowVariable:intOne}, {WorkflowVariable:intTwo},{WorkflowVariable:intThree}, {WorkflowVariable:mltOutputParameter1}, {WorkflowVariable:mltOutputParameter2} output

 

The name "output" at the end must only be provided once without any semicolons at the end

While giving the input and output parameters only provide the values and not the field names.

And on the results in the desired variables.

 

 

 

 
 
0 Kudos
Reply
rodgonzalez
Nintex Newbie

Re: Execute SQL - Stored Procedure with Variables as Parameters

Jump to solution

I have not worked yet I have put it as you say at the end, it may be because of the ORACLE database and that functionality does not accept it here.

 

Error al ejecutar una operación de base de datos. ORA-06550: line 1, column 318:
PLS-00103: Encountered the symbol "OUTPUT" when expecting one of the following:

   . ( ) , * @ % & = - + < / > at in is mod remainder not rem
   <an exponent (**)> <> or !=

 

Screenshot_2.jpg

 

0 Kudos
Reply