How can I execute stored procedures in blue prism, changing Global Code of Data SQL Server Business Object??

[GonzaloFernánde]

Hello everyone, first of all, allow me to introduce myself. My name is Gonzalo Fernández and I started headquarters a year ago in the world of RPA. I am still a novice in these matters and my manager has asked me to do a task that I do not know how to do.

The topic at hand is how to run a stored procedure with input parameters (an indeterminate number of parameters). For example, I have created a stored procedure in SQL Server that has as input parameters: the color, the country and the cylinder capacity of a vehicle. This procedure performs a query on the Vehicles table, returning a table filtered by those parameters. Then I have also created a procedure that performs an insert in the Vehicles table. Then in Blue Prism I modified the Get Collection and Execute action of the Bussines Object: Data SQL Server. In this way I managed to do what they asked me to do. But it was not the correct signature as they wanted me to use only one action: Execute, to execute any stored procedure.

As you can see, the question is how to execute procedures regardless of whether they return a table or not, and that they can execute a query, an insert or a modification, but the important thing is that this procedure has input parameters.

For this reason, my manager has asked me to make a modification of the Business Object "Data -SQL server" and using the Execute action, modify in some way the Global Code of this Business Object to allow a stored procedure to have input parameters and return or not a table.

Attached images:

This is the table:

First  stored procedure:
CREATE PROCEDURE Insertar_vehiculo_marca_pais_color_cilindrada
@id char(7),@marca varchar(40),@modelo varchar(40),@pais varchar(40),@color varchar(40), @cilindrada int
AS
Insert Into [Vehiculos] ([Id]
,[Marca]
,[Modelo]
,[Pais]
,[Color]
,[Cilindrada])
Values (@id,@marca,@modelo,@pais,@color,@cilindrada)
GO

EXEC dbo.Insertar_vehiculo_marca_pais_color_cilindrada
'AZX7536','Reanult','Laguna','España','Gris','2000'

Second stored procedure:
CREATE PROCEDURE [dbo].[Vehiculos_select_marca_pais_color_y_cilindrada]
@marca varchar(40),@pais varchar(40),@color varchar(40),@cilindrada int
AS
SELECT TOP (1000) [Id]
,[Marca]
,[Modelo]
,[Pais]
,[Color]
,[Cilindrada]
FROM [Concesionario].[dbo].[Vehiculos]
WHERE Marca=@marca
AND Pais=@pais
AND Color=@color
AND Cilindrada=@cilindrada

EXECUTE Vehiculos_select_marca_pais_color_y_cilindrada 'Seat', 'España','Blanco','1500'


Code of Execute (Modified), The key to this code is the construction of the SQL query, so that this is executed: "EXECUTE" & [NameProcedure] & "
@ "& [Parameter1] &" = '"& [ParameterValue1] &"', @ "& [Parameter2] &" = '"& [ParameterValue2] &"', @ "& [Parameter3] &" = '"& [ValueParameter3] & "'"
The inputs are the name of the stored procedure and a collection. This collection includes a set of variables and their corresponding value, for example: Color - White, Country - France, etc.

Global Code of Data SQL Server

-----------

Private moConnection As SqlConnection
Private moTransaction As SqlTransaction

Private Sub WriteCsv(ByVal sql As String, ByVal out As TextWriter)

	' The target data set
	Dim ds As New DataSet()

	' Build and execute the command, dumping the data into the above set
    Using cmd As New SqlCommand()
        cmd.Connection = moConnection
        cmd.Transaction = moTransaction
        cmd.CommandText = sql
        
        Using da As New SqlDataAdapter()
            da.SelectCommand = cmd
            da.Fill(ds)
        End Using
	End Using

	' Go through the data, escaping and outputting to the writer
	Dim quotesRequiredChars() As Char = {","c, """"c}
	For Each tab As DataTable In ds.Tables
		' Column headers first
		Dim first As Boolean = True
		For Each col As DataColumn In tab.Columns
			If first Then first = False Else out.Write(",")
			Dim val As String = col.ColumnName
			If val.IndexOfAny(quotesRequiredChars) >= 0 Then _
			 val = """" & val.Replace("""", """""") & """"
			out.Write(val)
		Next
		out.WriteLine()
		' Then a line for each row
		For Each row As DataRow in tab.Rows
			first = True
			For Each col As DataColumn in tab.Columns
				If first Then first = False Else out.Write(",")
				If row.IsNull(col) Then Continue For
				Dim val As String = row(col).ToString()
				If val.IndexOfAny(quotesRequiredChars) >= 0 Then _
				 val = """" & val.Replace("""", """""") & """"
				out.Write(val)
			Next
			out.WriteLine()
		Next
		' Separate each table with a couple of blank lines
		out.WriteLine()
		out.WriteLine()                
	Next
	
	out.Flush()

End Sub

Private Function GetScalar(Of T)(ByVal sql As String, _
 ByRef success As Boolean, ByRef msg As String) As T

	' Assume Success
	success = True
	msg = ""

	Try
		Using cmd As New SqlCommand()
			cmd.Connection = moConnection
			cmd.CommandText = SQL
			cmd.Transaction = moTransaction
			Return CType(cmd.ExecuteScalar(), T)
		End Using
	Catch ex As Exception
		success = False
		msg = ex.Message
		Return Nothing
	End Try

End Function​

-----------

Greetings

PD: Sorry for the inconvenience caused by a text that is too long, I wanted to detail it as much as possible.


------------------------------
Gonzalo Fernández
------------------------------

[ewilson]

Hi Gonzalo,

Welcome to the Community! I may have missed it, but I don't see in your code where you're set the SQL command type to indicate that it is a stored procedure that you're trying to execute. Here's an example:

SqlCommand sql_cmnd = new SqlCommand("PROC_NAME", sqlCon);  
sql_cmnd.CommandType = CommandType.StoredProcedure;  
sql_cmnd.Parameters.AddWithValue("@FIRST_NAME", SqlDbType.NVarChar).Value=firstName;  
sql_cmnd.Parameters.AddWithValue("@LAST_NAME", SqlDbType.NVarChar).Value=lastName;  
sql_cmnd.Parameters.AddWithValue("@AGE", SqlDbType.Int).Value = age;  
sql_cmnd.ExecuteNonQuery(); ​

​
As for supporting dynamic input values, there are a couple ways you could do this. One would be to pass the values in via a Collection. A Blue Prism Collection is the equivalent of a .NET DataTable. So, you could populate the Collection in the process, pass it into the VBO, and then within the Code stage you could iterate over the DataTable pulling out the values and adding them to your stored procedure input.

Another way to do it would be to just pass it all in as some sort of delimited string (ex. pipe delimited). Then in the Code stage you could use the String.Split() method to return an array of strings to use to populate your stored procedure input.

Cheers,

------------------------------
Eric Wilson
Director, Partner Integrations for Digital Exchange
Blue Prism
------------------------------

[GonzaloFernánde]

Hi Eric,

As for your statement "... but I don't see in your code where you're set the SQL command type to indicate that it is a stored procedure that you're trying to execute ...". The thing is, I haven't even set a SQL command type to indicate that it is a stored procedure that I am trying to execute. I mean, the Global Code part of Data SQL Server, I have not modified it. More than anything because I don't know what to introduce or modify in the code to allow a stored procedure with inputs to be executed. That screenshot in which you see a flow, it was my way of doing it without using Global Code, the problem with that flow is that it couldn't return a table if the stored procedure is a select and not an insert.

Anyway, thank you very much for answering, I will try the two solutions that you have shown me.

Greetings.

------------------------------
Gonzalo Fernández
------------------------------

[LukasMaier1]

Hi Eric,

Thank you for your comment! I already build the process as suggested by your post. In my case a get a variable back from the exec of my usp. 

How can I store the result data of the stored procedure into a blueprism variable? I cannot find any output variable.

Kind regards,
Lukas​

------------------------------
Lukas Maier
------------------------------

[ewilson]

@Lukas Maier,

If you expect to receive a return value from your stored procedure you need to add one extra parameter to your SQL Command definition. This parameter needs to have a parameter direction of ReturnValue. Example:

SqlCommand sql_cmnd = new SqlCommand("PROC_NAME", sqlCon);  
sql_cmnd.CommandType = CommandType.StoredProcedure;  
sql_cmnd.Parameters.AddWithValue("@FIRST_NAME", SqlDbType.NVarChar).Value=firstName;  
sql_cmnd.Parameters.AddWithValue("@LAST_NAME", SqlDbType.NVarChar).Value=lastName;  
sql_cmnd.Parameters.AddWithValue("@AGE", SqlDbType.Int).Value = age;  

// Define the return parameter.
var returnParameter = sql_cmnd.Parameters.Add("@ReturnVal", SqlDbType.Int);
returnParameter.Direction = ParameterDirection.ReturnValue;

sql_cmnd.ExecuteNonQuery(); ​​

Cheers,

------------------------------
Eric Wilson
Director, Integrations and Enablement
Blue Prism Digital Exchange
------------------------------

[LukasMaier1]

Hi Eric,

Thank you for your quick reply and the help! Managed it perfectly with the Get Text page from SQL Server VBO. 

Best regards,
Lukas

------------------------------
Lukas Maier
------------------------------