How to run a Procedure with output in php with mssql driver

Asked

Viewed 1,744 times

1

I’m trying to run a sql server database that returns a variable to me. 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 my SP OUTPUT:

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 run in PHP, using the driver for sql server, sqlsrv_query, for example. But in sqlsrv_query I am only able to execute one instruction at a time.

php

  • Would that be it? mssql_query('exec SP_TESTE @Externalfieldid', $con);

  • Sorry, misspelled, need the sqlsrv driver (had posted mssql by mistake)... Apparently it only allows me to do a query execution...

1 answer

1

It makes PDO work.

Opens 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 run the trial and do whatever you want 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 necessary with the driver itself, no 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 doesn’t work with any of the above methods, you will need to use Pdostatement::nextRowset(void) or sqlsrv_next_result().

Microsoft has a page only with driver documentation SQLSRV.

See here an example of the driver application.

Browser other questions tagged php

You are not signed in. Login or sign up in order to post.