Readyapi(Groovy) Database Request: Execute stored procedure with TVP(Table Valued Parameter) as parameter

Question

I need to call a stored procedure in readyapi(Groovy) step and wasn't successful in executing. Can someone help.

DECLARE @PNList TBL_SList
INSERT @PNList (StringValue) VALUES ( '123')
exec Test_Overview @PNList, '713'

How to use SQL Server Management studio - "Execute Stored Procedure" for User Defined Table Types, by right clicking a stored procedure in SSMS and enter values in the UI? How to pass value of user defined table type in the Value field?

https://www.javadoc.io/doc/com.microsoft.sqlserver/mssql-jdbc/6.1.7.jre8-preview/com/microsoft/sqlserver/jdbc/SQLServerDataTable.html

Answer 1

Found out a way to execute from ReadyAPI using Groovy script. Hope this helps someone in future.

import java.sql.*
import com.microsoft.sqlserver.jdbc.*
def serverName  = "server01"
def instance    = ""
def dbName = "db01"
dbURL = "jdbc:sqlserver://" + serverName+ ":1433;instanceName=" + instance + ";databaseName=" + dbName + ";integratedSecurity=true"
try
{
    DriverManager.registerDriver(new com.microsoft.sqlserver.jdbc.SQLServerDriver())
    Connection conn = DriverManager.getConnection(dbURL);   
    String sql = "EXEC dbo.Test_Overview ?";
    PreparedStatement stmt = conn.prepareStatement(sql)
    SQLServerDataTable accounts = new SQLServerDataTable();
    accounts.addColumnMetadata("StringValue", java.sql.Types.VARCHAR);
    accounts.addRow("614");
    ((SQLServerPreparedStatement) stmt).setStructured(1, "dbo.TBL_SList", accounts);
    ResultSet rs = stmt.executeQuery()
   while (rs.next()) {
       log.info rs.getInt(1);
   }    
    conn.close()
}
catch(Exception e)
{
    log.error "Could not connect to the database: " + e
}