I haven't dealt with stored procedures at all directly with Execute SQL, but this blog post covers it well:
What might be better for you is using the "import records" script step to pull the data result of your SQL over to filemaker.
Traditionally I've used the ExecuteSQL script step whenever I need to write data FROM FileMaker back to SQL and don't want to deal with ESS via the relationship graph.
I'm assuming the results can only come into a single field within FileMaker (which isn't ideal).
It is important to note that the Execute SQL script step will not return a result. It is meant to send / update data. Only the Import script step can return data from an ODBC data source.
It has been a while... does the driver have "logging" and that shows "something" after INSERT, UPDATE or DELETE?
However, the script step has nowhere to place any results, so this is a true statement.
You treat it like straight text concatenation with the single quotes being just text in your string.
if there are no variables, but you have given the exact syntax needed, then just put it in quotes just as it appears. If you need to feed it Filemaker data as variables each time you run it then do the following:
For example let's say the CTDS was stored in your filemaker data as mytable::field1 and the date was mytable::field2. You would send this calculated value to the ExecuteSQL script step:
"Exec iSM_Query6 ‘" & mytable::field1 & "’, ‘" & mytable::field2 & "’"
I left the single quotes formated as you had them because it's easier to see in my example, but you should probably type them to avoid wierdness with SQL misinterpreting them. To validate your syntax, if you create the string just like I did there and look at it in your data viewer on FM Advanced while positioned on a particular record, you should see the exact SQL command you're looking for.
You talked about returning results. As others have said, if the SQL stored procedure is a select statement which is going to return a set of results, then you use the same syntax as I just gave you, but rather than using the ExecuteSQL script step, you use the Import Records script step. Pull down the drop-down that says "File..." and choose "ODBC Data Source." You have to have the login credentials to use that ODBC data source, and you have to have set up that ODBC data source on the client you're on. Unlike Filemaker's ESS feature, a server side ODBC driver will not work for running SQL stored procedures. So assuming you have set up your computer this way, then you can import records using the syntax I gave you above.
As far as getting the results into discrete fields, your assumption is not correct that they would have to go into a single field. If the stored procedure contains a select statement, it would return fields with field names just as though you were importing from a Filemaker table, and you can map them to their proper destination in your Filemaker table.
I often test the query the first time by importing not to my existing data but to a "Create New Table" destination. Then I can examine everything before deciding whether to just use this new table in FM and create relationships to it or to import the SQL data directly into existing tables.
Construct the SQL as ronhofius stated. Then depending on what your stored procedure does, just import the result with an odbc.
I have used the technique do let MS-SQL collect time-sheets entries and use a transaction to test and set a "flag" field, which I used later in an ODBC to import the collected records from MS-SQL. So the stored procedure did not return any results itself (you can put a lot in a stored procedure, which is pretty powerful, cause FileMaker has nothing to do with it.)
If your stored procedure is just a query, you might be able to put that into the import from ODBC source and skipt the stored procedure. You can also make the stored procudure create a new table with the ids you need from other tables and use that in the FileMaker SQL query using a SELECT ... FROM ... WHERE id IN (SELECT id FROM tempTable ...)
Point is the FIleMaker SQL is a small subset of that mighty SQL language :-)
You use different chars at opening and closing. Try use same one.
' = char(39)
What result do you get when this is performed ON the SQL server? is the stored procedure a SELECT, and could you simply use the correct query 'as is' with the Import script step?
If it has some other steps (unseen by us), then you might see if your DBA can make it a "View" upon which you can SELECT with the Import script step. Tables and Views can be used by ESS or ODBC and the Import script step.