Database stored procedure

[#1]

Hi There,

I have a database in which I wrote a stored procedure, which generates a table for DOE analysis. Basically selecting the parameters and their values dynamically, which is why a simple table query does not work.

In JMP 13, there is the advance database open table window which lists all the stored procedure in the SQL. I'd like to run "Call StoredProc(x)" by using execute sql but nothing happens. In matlab it gives back the proper table.

How can I execute a stored procedure in JMP and import the results into a table?

Thanks,

Akos

[#2]

I have made such an interface which relies on a stored procedure to return the data once the user choices are known. The connection string was obtained by connecting to a table on the same server as the stored procedure and then copying it from the table source script.

I then made use of the ability to execute SQL code from JMP. The example below is for a server running Microsoft T-SQL. All my inputs to the stored procedure are text hence the use of '' around each string. This a bit brute force as you are using JSL to write SQL strings but it works well.

//get user inputs as strings

...interface code here...

//create connection
dbc = Create Database Connection(
"ODBC:Description=##### toolset;DRIVER=SQL Server;SERVER=#####;UID=######;Trusted_Connection=Yes;APP=JMP;WSID=####;DATABASE=#####;"
);

//get result
Eval(
Parse(
Eval Insert(
"\[
SQLString = "DECLARE @rc int

EXECUTE @rc = [dbo].[GetResultsStandardised]
@DataSource = '^dataSource^'
,@Site = '^Site^'
,@FromDate = '^fromDateString^'
,@ToDate = '^toDateString^'
,@Level1 = '^level1string^'
,@Level2 = '^level2string^'
,@Level3 = '^level3string^'

";
]\"
)
)
);

dtresult = Execute SQL( dbc, SQLString, "Result Table" );

//tidyup
Close Database Connection( dbc );