topic Re: How to Call SQL Server Stored Procedures in JMP 18.1.1 in Discussions

How to Call SQL Server Stored Procedures in JMP 18.1.1

BryGuy63 — Mon, 16 Dec 2024 20:01:55 GMT

Hello - I'm a relative newbie to JMP, but need assistance establishing a connection to several SQL Server database stored procedures. The SPs have a few input parameters that need to be supplied at runtime, and they will generate recordsets of data based on that. I've tried using just the ODBC System DSN approach, but the SPs aren't exposed there, so I've read that JSP is perhaps an option. Any snippets of code or best practices are very appreciated.

Re: How to Call SQL Server Stored Procedures in JMP 18.1.1

pmroz — Wed, 18 Dec 2024 16:24:55 GMT

Not sure if this will work for SQL Server, but for Oracle you can do this:

sql_statement = "BEGIN myusername.pkg_util.send_email('Success'); END;";
execute sql(dbc, sql_statement);

Control will return to JMP when the procedure is done.

Re: How to Call SQL Server Stored Procedures in JMP 18.1.1

Jed_Campbell — Thu, 19 Dec 2024 16:52:04 GMT

The syntax is a little different for SQL Server. In the example below, I'm asking the AdventureWorks2019 database (a standard one supplied by Microsoft) to execute the stored procedure "USPGetEmployeeManagers" for BusinessEntityID 285 (a person named Syed Abbas). The script returns the table in the screenshot.

//dbc copy/pasted from a different sql query made by Query Builder
dbc = Create Database Connection ("ODBC:APP=JMP;DATABASE=AdventureWorks2019;DESCRIPTION=SQLEXPRESS;DSN=AdventureWorks;TRUSTED_CONNECTION=Yes;WSID=mycomputername;");

//sql copy/pasted from running stored procedure in SQL Server Management Studio
sql_statement = "
DECLARE	@return_value int

EXEC	@return_value = [dbo].[uspGetEmployeeManagers]
		@BusinessEntityID = 285

SELECT	'Return Value' = @return_value
";

//run the stored procedure
execute sql(dbc, sql_statement);

Re: How to Call SQL Server Stored Procedures in JMP 18.1.1

BryGuy63 — Fri, 20 Dec 2024 17:47:06 GMT

Hi Jed - I appreciate the detailed response, including an example with an SP parameter. I'll assume that if the SP returns multiple rows for a given set of parameters, that can be dealt with efficiently within JMP.

Re: How to Call SQL Server Stored Procedures in JMP 18.1.1

Jed_Campbell — Fri, 20 Dec 2024 20:32:15 GMT

Yes, the JMP table captures however many rows the query sends back.