MS SQL Server StoreProcedure returning resultset

Question

MS SQL Server StoreProcedure returning resultset

Navigation

#1 by (1 votes)
#2 by (1 votes)

0

I am new to the SQL server and I need to create an SP that processes certain tables and writes changes and then returns the results of these changes to me as a table-value function for PHP. I tried this:

        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>");
    }
    ?>

However nothing returned; if I run the command in the manager studio it returns the resultset of temp_table as expected.

php sql-server

asked by anonymous 12.01.2017 / 15:06

2 answers

Website with animation [closed] Changing properties of a div with a click

score 1 · Answer 1

Initially I would put a parameter in arrayx() to handle the return of SP. Use the sqlsrv_next_result() function to see if something is returned by the bank, if you do 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 should look like this:

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

score 1 · Answer 2

I found it !!! the sql server returns a resultset for each command within the SP, this also includes the insert / updates / deletes among others, informing the amount of affected lines.

For this you have to navigate between the resultssets as in the link link

To run my gambiarra, that is, my code looks like this:

<?
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 jumping the insert results and when he arrived in RS I wanted him to immediat the values of select