How to execute a procedure with output in php with mssql driver

Question

How to execute a procedure with output in php with mssql driver

Navigation

#1 by (1 votes)

1

I'm trying to run a sql server procedure that returns me a variable. The procedure, for example has the following structure:

CREATE PROCEDURE SP_TESTE
@ID INT,
@REFERENCIA INT OUTPUT
AS  
    SELECT @REFERENCIA = ID_REFERENCIA
    FROM REFERENCIA  WHERE ID_REFERENCIA = @ID;
    RETURN
GO

In SQL ManagementStudio, I use the following query to return the OUTPUT of my SP:

DECLARE @REF INT
EXEC SP_TESTE 1,@REFERENCIA = @REF OUTPUT
SELECT @REF AS REFERENCIA

Now I would like to know how to pass this query to be executed in PHP, using the driver for sql server, sqlsrv_query, for example. But in sqlsrv_query I'm only able to execute one statement at a time.

php

asked by anonymous 14.05.2016 / 04:47

1 answer

One-hour character counter How can I see the versions of packages updated via composer?

score 1 · Answer 1

Make PDO work.

Open the connection:

<?php

try {
    $con = new PDO('sqlsrv:Server=$SERVIDOR;Database=$BANCO_DE_DADOS', $USUARIO, $SENHA );
    $con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (Exception $ex) {
    print_r('Error: ' . $ex->getMessage());
}

Then execute the procedure and do as you please with the data;

$query = "exec SUA_PROCEDURE";

$prepare = $con->prepare($query);

$prepare->execute();

$result = $prepare->fetchAll(PDO::FETCH_ASSOC);

//abaixo só se precisar iterar pelo resultado, se não pode usar o array retornado em $result

foreach ($result as $r => $val) {
    $resultado[] = $val;
}

If you need the driver itself, without PDO:

First, open the connection:

<?php

/**
 *
 * abre a conexao
 */
$con = sqlsrv_connect(
    'SERVIDOR',
    array(
        "UID"=>"USUARIO",
        "PWD"=>"SENHA",
        "Database"=>"BANCO_DE_DADOS",
        "CharacterSet" => "UTF-8"
    )
);

//testa a conexao
if( $con === false){
    echo "Conexao nao estabelecida: ";
    die( print_r( sqlsrv_errors(),true));
}

Then run the query:

$query = "exec PROCEDURE";

$exec = sqlsrv_query($con, $query);

while($r = sqlsrv_fetch_array($exec, SQLSRV_FETCH_ASSOC)){
    print_r($r);
}

One more detail, depending on your case, if it does not work with any of the above methods, you'll need to use PDOStatement :: nextRowset (void) or sqlsrv_next_result () . >

Microsoft has a page with only the documentation driver SQLSRV .

See here for an example driver application.