How to execute stored procedure with parameter output

[Eder_DeLima]

I need to execute a stored procedure that contains an output parameter as shown in the example:

sp_test 'Name', 'Address', @id output

The @id variable contains a sequential id. How can I run it through Blue Prism?

[ewilson]

Hello @Eder_DeLima,

There are standard VBOs for working with different database systems available on the Digital Exchange (ex. Data - SQL Server, Data - OLEDB, Data - SQLite, etc). Unfortunately, most of these do not include actions for executing stored procedures. They are designed to run straight SQL statements. With that said, you could always extend an existing VBO to include an action that will execute a stored procedure.

What database platform are you looking at?

Cheers,
Eric​

[devneetmohanty07]

Hi @Eder_DeLima,

You can execute the stored procedure using 'Execute' action from 'SQL Server - VBO' if you are using Microsoft SQL Server.

Let me show an example:

In my case I have an Employee database with majorly four columns: Employee ID (Auto incremented field), Employee Name, Employee Department and Employee Salary. Now what I essentially want is to only insert new records if the record is not already present in my table. Currently, this is how my table looks like:


Now, I will create a stored procedure for the table which will accept three input parameters for Employee Name, Employee Department and Employee Salary as shown below. Here the name of the stored procedure is 'INSERT_EMP_RECORD' :



In this stored procedure, I am checking first if any record with the given input parameters exists or not. If it does not exists, then I am inserting the records only.

Now, in your Blue Prism Process Studio you can create the following workflow:



Here I have taken three variables which will be mapped to the three input parameters going ahead and I am using values such that a new record will be inserted. Now, in order to execute the Stored Procedure, I will use the following query:

"DECLARE @return_value int EXEC @return_value = [dbo].[INSERT_EMP_RECORD] @name = N'" & [Employee Name] & "', @dept = N'" & [Employee Department] & "', @salary = " & [Employee Salary]

NOTE: Here, N character is used to denote string values which need to be provided within single quotes preceded by this character.

Upon executing the workflow you should be able to see a new record getting inserted as shown below:





------------------------------
----------------------------------
Hope it helps you out and if my solution resolves your query, then please mark it as the 'Best Answer' so that the others members in the community having similar problem statement can track the answer easily in future

Regards,
Devneet Mohanty
Intelligent Process Automation Consultant | Sr. Consultant - Automation Developer,
WonderBotz India Pvt. Ltd.
Blue Prism Community MVP | Blue Prism 7x Certified Professional
Website: https://devneet.github.io/
Email: devneetmohanty07@gmail.com

----------------------------------
------------------------------​