nested stored procedure with database toolbox returns error "The statement did not return a result set."

I wrote a nested stored procedure (sproc) in MS SQL Server. The first sproc getNeighbors() goes through a network collecting all neighbors recursively by a single SELECT statement. I can call this sproc from Matlab with no problems. I made a second sproc getNetwork() that calls the first sproc to find the neighbors, and then finds all the connections between those neighbors. This second sproc works fine in SQL Server Management Studio. In Matlab it gives an error saying the statement dit not return a result set. I use Database Toolbox 4.0 on R2012b.

The second sproc is the following:

CREATE PROCEDURE getNetwork
  @centralActorId int, 
  @maxDepth int
AS

DECLARE @neighbors TABLE(
  actorId int,
  generation int,
  depth int)
INSERT @neighbors
  EXEC getNeighbors
    @centralActorId=@centralActorId,
    @maxDepth=@maxDepth

SELECT  n1.actorId as friendingActorId,
  n2.actorId as friendedActorId
FROM   @neighbors n1 JOIN
  friend_relations fr ON n1.actorId=fr.friendingActorId JOIN
  @neighbors n2 ON fr.friendedActorId=n2.actorId
ORDER BY n2.actorId

In Matlab I use the syntax

DatabaseConn = database('friend_database','','', 'Vendor','Microsoft SQL Server','Server','DATASRV', 'AuthType','Windows');
resultSet = fetch(exec(DatabaseConn,'{call getNeighbors(154,2)}'))

which results in

resultSet =

          Attributes: []
                Data: {{304x3 cell}}
      DatabaseObject: [1x1 database]
            RowLimit: 0
            SQLQuery: '{call getNeighbors(154,2)}'
             Message: []
                Type: 'Database Cursor Object'
           ResultSet: [1x1 com.microsoft.sqlserver.jdbc.SQLServerResultSet]
              Cursor: [1x1 com.mathworks.toolbox.database.sqlExec]
           Statement: [1x1 com.microsoft.sqlserver.jdbc.SQLServerStatement]
               Fetch: [1x1 com.mathworks.toolbox.database.fetchTheData]

However, when running

>>resultSet2 = fetch(exec(DatabaseConn,'{call getNetwork(154, 2)}'))

Error using database/exec (line 52)
The statement did not return a result set.

The identifier is database:database:cursorError

When reading about this online, some of the solutions pointed to the fact that the INSERT followed by a SELECT produces multiple return sets (not quite what the error suggests though). Therefore I tried

resultSet2 = fetchmulti(exec(DatabaseConn,'{call getNetwork(154, 2)}'))

But that gives the same error. But now comes the twist. When altering the sproc, adding the line

SELECT '' AS justSomeText;

before the DECLARE @neighbors ... part, I get the data just fine: both the data.

ans =

          Attributes: []
                Data: {{1x1 cell}  {1012x2 cell}}
      DatabaseObject: [1x1 database]
            RowLimit: 0
             ...

where ans.Data{1} is an empty string, and ans.Data{2} is the network table.

Does anyone know how to prevent Matlab from disregarding all subsequent result sets, in case the first statement does not return data? Or alternatively, of a workaround in SQL Server that doesn't give the empty table first.

Answer 1

[Pieter-Laurens Baljon]

Adding

SELECT '' AS dummyText;

in the query on the Matlab end also works and leaves the sproc open for further nesting.

In addition, setting

SET NOCOUNT ON;

in the sproc prevents Matlab from missing subsequent result sets.