Invoking a stored procedure from a sql db via katalon

I have a test case where I used to reset test data - unfortunately, this only works in the dev environment b/c the db uses my credentials and I don’t have write credentials in prod (nor should I have them). So, we wrote a stored procedure to invoke that has tightly controlled credentials/limitations, and now I’m having trouble converting my db call to instead invoke a stored proc.

so I want to invoke a stored proc like EXEC [dbo].[stored_procedure_name] 'user@email.test'

Where user@email.test is the name of a user i’m resetting the test data on. I’ve spent a significant amount of time researching this and can’t find a good implementation, or even a working one. Any help appreciated!

fwiw - this particular stored proc just writes to the db, it does not return any data.

update: I’ve added a keyword per the documentation I can find, it looks like this:

import java.sql.DriverManager
import java.sql.ResultSet
import java.sql.Statement

import com.kms.katalon.core.annotation.Keyword

public class StoredProc {
	private static Connection connection = null;

	/**
	 * Open and return a connection to database
	 * @param dataFile absolute file path
	 * @return an instance of java.sql.Connection
	 */

	//Establishing a connection to the DataBase

	@Keyword

	def connectDB(String url, String dbname, String port, String username, String password){

		//Load driver class for your specific database type

		String conn = "jdbc:sqlserver://;servername=xxxxx;port=xxxxx;databaseName=xxxxx;integratedSecurity=xxxxx;"


		//Class.forName("org.sqlite.JDBC")

		//String connectionString = "jdbc:sqlite:" + dataFile

		if(connection != null && !connection.isClosed()){

			connection.close()

		}

		connection = DriverManager.getConnection(conn, username, password)

		return connection

	}

	/**
	 * execute a SQL query on database
	 * @param queryString SQL query string
	 * @return a reference to returned data collection, an instance of java.sql.ResultSet
	 */

	//Executing the constructed Query and Saving results in resultset

    @Keyword

    def executeQuery(String queryString) {

        Statement stm = connection.createStatement()

        ResultSet rs = stm.executeQuery(queryString)

        return rs

	}

	//Closing the connection

	@Keyword

	def closeDatabaseConnection() {

		if(connection != null && !connection.isClosed()){

			connection.close()

		}

		connection = null

	}

	/**
	 * Execute non-query (usually INSERT/UPDATE/DELETE/COUNT/SUM...) on database
	 * @param queryString a SQL statement
	 * @return single value result of SQL statement
	 */

	@Keyword

	def execute(String queryString) {

		Statement stm = connection.createStatement()

		boolean result = stm.execute(queryString)

		return result

	}

}

i’m able to successfully connect to the db and make the stored proc call but am getting error like this (below) because I need to pass an email address required by the proc as a variable, and don’t know how to do this:

here’s how i’m invoking the stored proc:

    'xxxxx', 'xxxxx', '', '')

CustomKeywords.'com.c.StoredProc.execute'('my_custom_stored_proc')('user@google.test')

CustomKeywords.'com.c.StoredProc.closeDatabaseConnection'()```

here's the error:

```Test Cases/Steps/Database/Database Reset Users Stored Procedure FAILED.
Reason:
org.codehaus.groovy.runtime.InvokerInvocationException: com.microsoft.sqlserver.jdbc.SQLServerException: Procedure or function 'my_custom_stored_proc' expects parameter '@userEmailAddress', which was not supplied.```



any tips on how to insert that email address into that custom key word that invokes the stored proc?

here’s the tutorial that led me to inserting the custom key word: https://docs.katalon.com/katalon-studio/tutorials/connect_db_gui_testing.html

starting a new thread to improve how i’m displaying my issues since i’ve made some progress.