SQL Server Partial Resultset

Ignition
1

Hi,

We use microsoft SQL server and ignition 7.9.17. We have a stored procedure that return immediately a partial resultset and the complete result a few seconds later.
There is an example below. I use system.db.runQuery(…), but Ignition only received the first partial result. The complete result is missing.
Do you have any idea how to get partial result and then the complete result a few second later?

Thanks

CREATE PROCEDURE #Proc
AS
BEGIN
    SELECT 1 AS PartialResult;

    RAISERROR('',0,1) WITH NOWAIT;

    WAITFOR DELAY '00:00:05'

    SELECT 2 AS FinalResult;
END;
2

I have to admit I did a double take when I saw that code. IMHO it looks evil and has a huge code smell by having a single sproc effectively return two different classes of data (Summary and Full).

My personal advice would be to refactor it into two separate sprocs - one for the partial result and one for the full result. And then create two separate system.db.runQuery() calls to get each type of result.

However I am nervous that generating your partial result is causing side effects in the DB that are then used when returning the full result set, in which case the refactoring may or may not not work depending on the underlying DB systems.

3

I agree with @peter that this is probably better as two separate sprocs.

I don’t think it will matter, because I believe these functions are only expecting one result set and so this probably won’t work, but there is a system.db.execSProcCall() that you should use to call SProc’s directly as opposed to runQuery. Using that might change your results.