Help with using a SQL stored procedure to pull data using JSL

[#1]

Good evening,

I'm a beginner to JSL.  I think suspect that I have a syntax error or something.  

I'm trying to run an SQL stored procedure from a JSL script.  My database connections seem to work correctly (see when the query is a select statement).  However, I can't get the stored procedure to run and give me the data table.  Below I've listed:

1) the query formula that works (the select statement) and

2) the three query formulas that won't run my stored procedure.

Thank you for taking a look.  --Birdi (JMP 17.0.0)

// HELP:  The select statement works but the stored procedure doesn't.  
//Does anyone have an idea as to what I'm missing?
 
// I can run the select statement, but I could use some help on running the stored procedure.
//query="SELECT * FROM [dbo].[tblCost_Outliers]"; // works
query = "exec dbo.procCost_PM_Birdi"; // doesn't work
//query="begin dbo.procCost_PM_Birdi; end;"; // doesn't work
//query = "dbo.procCost_PM_Birdi"; // doesn't work
 
 
//create connection
dbc = Create Database Connection(
	"DSN=QIS_Norpac;UID=sqr;PWD=reports;APP=JMP;WSID=WALNP10491;DATABASE=Cost;"
);
 
//get result
Execute SQL( dbc, query ); 
 
//tidyup
Close Database Connection( dbc );

What does that mean to use <JSL> to insert JSL code?  Yep, I'm new.  Thank you for your patience.

[#2]

I'm not sure what database you're using.  I use Oracle which has different syntax, but there are some general principles that may be helpful:

• Execute SQL cannot return table data from a call to stored procedure directly.
• If your stored procedure creates a table, make a second call to retrieve that data.

This works in Oracle:

execproc = "begin myuser.my_oracle_proc; end";
query    = "SELECT * FROM myuser.mytable";
 
// Run the procedure to load the table tblCost_Outliers
Execute SQL( dbc, execproc ); 

// Retrieve the table into the variable dt
dt = Execute SQL( dbc, query ); 
 
//tidyup
Close Database Connection( dbc );

Maybe JMP tech support can help you with the correct syntax for calling a procedure for your database.

[#3]

See https://community.jmp.com/t5/Discussions/SQL-query-to-ODBC-datasource-SQL-server-with-temporary-tabl.... This will help you with this issue since you are using SQL Server.