Is there a way to execute a SQL Server stored procedure and get the result that this one gives me?
Thanks
Is there a way to execute a SQL Server stored procedure and get the result that this one gives me?
Thanks
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)
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