MS SQL server Storeprocedure returning resultset

Question

I am new to SQL server and I need to create an SP that does the processing of certain tables and save changes and after giving me the result of these changes as a table-value Function for PHP. tried that :

        create procedure auto_fill_teste
    as
    begin
        DECLARE @temp_table table(qtd int);

        insert into @temp_table 
        SELECT COUNT(*) FROM sysobjects;

        insert into @temp_table 
        SELECT COUNT(*) FROM sysindexes;    

        SELECT * FROM @temp_table;
    end

and in php I have the following codes

    <?
    class MsSQLConnection {
        protected $MSconn;
        protected $serverName = 'localhost';
        protected $connectionOptions = array("Database" => "master", "UID" => "as", "PWD" => "123456");
        public $query;

        function conecta_MSSQL() {
            $this->MSconn = sqlsrv_connect($this->serverName, $this->connectionOptions);
            if (!$this->MSconn) {
                var_dump(sqlsrv_errors());
                die;
            }
        }

        function executar($sql,$params = array(),$options = array("Scrollable" => "buffered")) {
            if (!$this->MSconn) {
                $this->conecta_MSSQL();
            }       
            $this->query=sqlsrv_prepare($this->MSconn, $sql, $params, $options);
            sqlsrv_execute( $this->query );

        }

        function arrayx($a = null) {
            if (empty($a))
                $a = $this->query;      
            return sqlsrv_fetch_array($a, SQLSRV_FETCH_BOTH);
        }
    }


    $conexao = new MsSQLConnection();

    $conexao->conecta_MSSQL();

    $conexao->executar("Exec dbo.auto_fill_teste");

    while($row = $conexao->arrayx(NULL)){
        print_r("$row <br>");
    }
    ?>

Yet nothing is returned; already if I run the command in manager studio it returns the resultset from temp_table as expected.

Answer 1

Initially put a parameter in arrayx() to treat SP return. Use the function sqlsrv_next_result() to know if there is something returned by the bank, if yes do the fetch_array() of all items and return to the end of the function.

function arrayx($a = null, $sp = false) {
    if (empty($a)) $a = $this->query; 

    if($sp){
        $next_result = sqlsrv_next_result($a);
        $itens = array();
        if($next_result){
            while($row = sqlsrv_fetch_array($a, SQLSRV_FETCH_ASSOC)){
                $itens[] = $row;
            }
        }

        return $itens;
    }       
    return sqlsrv_fetch_array($a, SQLSRV_FETCH_BOTH);
}

The flame must stay like this:

$conexao = new MsSQLConnection();
$conexao->conecta_MSSQL();
$conexao->executar("Exec dbo.auto_fill_teste");
$result = $conexao->arrayx(NULL, true);
print_r($result);

Answer 2

I found!!! sql server returns a result for each command within SP, this tbm include the Insert/updates/Deletes among others, informing the amount of affected lines.

for this you have to go navigating between the resultssets as in the link http://php.net/manual/en/function.sqlsrv-next-result.php

to work my gambit,:

<?
class MsSQLConnection {
    protected $MSconn;
    protected $serverName = 'localhost';
    protected $connectionOptions = array("Database" => "master", "UID" => "as", "PWD" => "123456");
    public $query;

    function conecta_MSSQL() {
        $this->MSconn = sqlsrv_connect($this->serverName, $this->connectionOptions);
        if (!$this->MSconn) {
            var_dump(sqlsrv_errors());
            die;
        }
    }

    function executar($sql,$params = array(),$options = array("Scrollable" => "buffered")) {
        if (!$this->MSconn) {
            $this->conecta_MSSQL();
        }       
        $this->query=sqlsrv_prepare($this->MSconn, $sql, $params, $options);
        sqlsrv_execute( $this->query );

    }

    function arrayx($a = null) {
        if (empty($a))
            $a = $this->query;      
        return sqlsrv_fetch_array($a, SQLSRV_FETCH_BOTH);
    }

    function  proximoResultset($a = null){
        if (empty($a))
            $a = $this->query;
        return sqlsrv_next_result($a);
    }
}


$conexao = new MsSQLConnection();

$conexao->conecta_MSSQL();

$conexao->executar("Exec dbo.auto_fill_teste");

while ($conexao->proximoResultset()) {
    while ($row = $conexao->arrayx(NULL, "both")) {
        print_r("$row[0] <br>");
    }
}
    ?>

With this he was skipping the results of Insert and when he arrived in the RS I wanted he foiled the values of select