How to Call SQL Server Stored Procedures in JMP 18.1.1

[#1]

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.

[#2]

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.

[#3 · Accepted solution]

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);

[#4]

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. 

[#5]

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