PHP SQLServer Stored Procedure Does Not Run Correctly

Question

PHP SQLServer Stored Procedure Does Not Run Correctly

Navigation

#1 by (0 votes)

2

Hello, I'm developing an application using Laravel Framework Handler from my Backend and found the following error.

My SqlServer Express 2008 R2 database has a stored procedure that performs a series of routines and inserts data already formatted into a table. The stored procedure receives a Key (Varchar) and the year (int) of the report I want to generate.

When I run the stored procedure through the database script the insert occurs correctly and I have all the data.

exec indicador.usp_indicador_101_1 'asdfgh', 2015

After executing the procedure I execute a method that performs the following select:

select * from indicador.T101_Indicador where A101_chave = 'asdfgh'

In the ideal scenario I have all the results returned correctly:

ButwhenIrunPHPwiththesamecode:

<?php$server="###.###.###.#";
$options = array(  "UID" => "usuario",  "PWD" => "senha",  "Database" => "meubanco");
$conn = sqlsrv_connect($server, $options);  

// Then execute the procedure
$proc = "exec indicador.usp_indicador_101_1 '147896311212', 2015";
$proc_result = sqlsrv_query($conn, $proc );
// Etc...
//mssql_free_statement($proc);
sqlsrv_close($conn);
?>

I have only the return of a line:

The Stored procedure does the inserts by itself, selecting the data is done at another time. The photos shown are selects made in the database script.

I have tried in several ways the execution of the stored procedure, my last attempt was this with pure PHP and the return is always the same, just a line.

Selection in the application is done by a method using Laravel features:

public static function T101_indicador($chave){

$dados = T101_indicador::where('A101_Chave', '=', $chave)
->orderBy('A101_Nomord')
->get();

//dd($dados);

return $dados;
}

php sql-server stored-procedures laravel sql-server-2008

asked by anonymous 19.12.2016 / 16:34

1 answer

Read and write in txt Permission and user levels in PHP and AngularJs (JS)

score 0 · Answer 1

I solved my problem, follow the new code:

$chave = (string) $chave;

$server = "enderecobanco";
$options = array(  "UID" => "usuario",  "PWD" => "senha",  "Database" => "bancodedados");
$conn = sqlsrv_connect($server, $options);

// Then execute the procedure
$proc = "{call $procedure(?,?)}";
  $params = array(&$chave, &$argumento);
  $statement = sqlsrv_prepare( $conn, $proc, $params);

sqlsrv_execute($statement);//Precisa executar Duas vezes para gerar todos os dados.
sqlsrv_execute($statement);

sqlsrv_close($conn);//fecha conexão

We needed to add "&" before the array of parameters, according to this > site and use the sqlsrv_execute () method twice, rather than sqlsrv_query (). I do not understand why double execution is necessary. Without this double execution the code does not have the correct result.