Can I run a SQL Server stored procedure in Katalon?

Is there a way to execute a SQL Server stored procedure and get the result that this one gives me?

Thanks

2 Likes

How to Run SQL Stored procedure in Katalon ?

Hello,
@balakrishna.s : you cannot run sql stored procedue in Katalon, you need database for that

@Paulo Andres Escobar Arroyo :
i think you are familiar with : https://www.katalon.com/resources-center/tutorials/connect-db-gui-testing/
then just expend custom keyword with function like this:

String query = "{call your_fancy_procedure(?)}"; CallableStatement statement = connection.prepareCall(query);  statement.setString(1, "all");  statement.execute(); 

(source: https://stackoverflow.com/questions/41997729/how-to-execute-a-procedure-with-jdbc)

2 Likes

Hi,
I Hope this information may help you-

Here is a way of capturing as many results from a stored procedure as you want. To demonstrate, I have taken an example of a stored procedure producing two results from MSDN and modified it slightly. To demonstrate that you really have full control over the result, I save both as a SQL Relational table. I use temporary tables in this example but you can do it any way you like.

–just in case it already exists, delete the procedure

IF Object_Id(‘Production.ProductList’, ‘P’) IS NOT NULL

DROP PROCEDURE Production.ProductList;

–Create the procedure

GO

CREATE PROC Production.ProductList @ProdName NVARCHAR(50),

/**

Summary: >

This is an example of a stored procedure that has multiple resultsets

that are returned, and which we can then use outside the procedure.

This works with AdventureWorks 2016

Author: PhilFactor (based on an example on MSDN that doesn’t)

Date: 07/03/2018

Database: AdventureWorks2016

Example: >

DECLARE @ProductAndListPrice NVARCHAR(MAX), @NumberOfOrders NVARCHAR(MAX);

EXEC Production.ProductList ‘%tire%’, @ProductAndListPrice OUTPUT,

@NumberOfOrders OUTPUT;

SELECT * FROM OpenJson(@ProductAndListPrice)

WITH (ProductID int ‘$.ProductID’, [Name] NVARCHAR(50) ‘$.Name’,

  ListPrice money '$.ListPrice')

SELECT * FROM OpenJson(@NumberOfOrders)

WITH ([Name] NVARCHAR(50) ‘$.Name’, NumberOfOrders INT ‘$.NumberOfOrders’)

Returns: >

Two JSON output variables

**/

– in this case we return two results, but there is no limit

– but each result needs to be saved in an output parameter

@FirstResult NVARCHAR(MAX) OUT, @SecondResult NVARCHAR(MAX) OUT

AS

BEGIN

– First result set saved as JSON in an output variable

SELECT @FirstResult =

(

SELECT Product.ProductID, Product.Name, Product.ListPrice

FROM Production.Product

WHERE Product.Name LIKE @ProdName

FOR JSON AUTO

);

– Second result set saved as JSON in an output variable

SELECT @SecondResult =

(

SELECT P.Name, Count(S.ProductID) AS NumberOfOrders

FROM Production.Product AS P

JOIN Sales.SalesOrderDetail AS S

ON P.ProductID = S.ProductID

WHERE P.Name LIKE @ProdName

GROUP BY P.Name

FOR JSON AUTO

);

END;

GO

– Execute the procedure

–first declare the variables that are destined to hold the JSON results

DECLARE @ProductAndListPrice NVARCHAR(MAX), @NumberOfOrders NVARCHAR(MAX);

–execute the procedure

EXEC Production.ProductList ‘%tire%’, @ProductAndListPrice OUTPUT,

@NumberOfOrders OUTPUT;

–create a temporary table to put it in and fill it with the first results

SELECT * INTO #ProductAndListPrice FROM OpenJson(@ProductAndListPrice)

WITH (ProductID int ‘$.ProductID’, [Name] NVARCHAR(50) ‘$.Name’, ListPrice money ‘$.ListPrice’)

–create a second temporary table to put it in and fill it with the second results

SELECT * INTO #NumberOfOrders FROM OpenJson(@NumberOfOrders)

WITH ([Name] NVARCHAR(50) ‘$.Name’, NumberOfOrders INT ‘$.NumberOfOrders’)

–now make sure that we have both results safely tucked away.

SELECT * FROM #ProductAndListPrice

SELECT * FROM #NumberOfOrders

We run it and voila!!

Regards,
Srija