How to execute store procedure from php with parameters

Question

Hi..  I'm execute from any app in php the next code:



<?php
require('conexion.php');   // this is the connection, it is fine
$params = array($id_cliente);  // $id_cliente it´s  any  var from .php
sqlsrv_query($conn, 'SpSendMail',$params);
?>




This is the store procedure:



ALTER  proc [dbo].[SpSendMail]
@p_cliente numeric
as
declare @mensaje varchar(60)
Declare @p_cantidad numeric
Declare @nomcli varchar(300)
DEclare @mailcli varchar(300)

Set @p_cantidad = (Select Count(*)  from table
where codcli = @p_cliente)

                 If @p_cantidad < 2
Begin 

set @mensaje = 'Your insurance policy.'
End
else
set @mensaje = Your insurance policies.'


Select top 1  @nomcli= nomcli, @mailcli = mailcli
From table Where codcli = @p_cliente


DECLARE @tableHTML  NVARCHAR(MAX) ;

SET @tableHTML =
N'<tr>
  <td><img src="http://misitioweb.com/image.jpg" width="800" height="140" /></td>
 </tr>
 <tr>
<td>&nbsp;
</td>
 </tr>
 <tr>
<td>
</td>
 </tr>'+
 
N' <tr>
<td class="est"><br><br>Dear Insured: ' +@nomcli+'
 <br />
 <br />
 <br /></td>
 </tr>'+
     
N'<tr>
<td class="cont">' +@mensaje+' <br /> <br />

any text...any text...any text <font color="#003366"><b>any text...any text..</b></font>.</td>
 </tr><br><br>'+
   
   
N'<table border="1">' +
N'<tr><th>title1</th><th>title2</th>' +
N'<th>title3</th><th>title4</th><th>title5</th>' +
N'<th>title6</th></tr>' +
CAST ( ( SELECT td = numpol,       '',
td = descrip, '',
td = dir, '',
td = nomram, '',
td = convert(char(10),vighas,21), '',
td = nomcia
 FROM table
 WHERE codcli = @p_cliente
 FOR XML PATH('tr'), TYPE 
) AS NVARCHAR(MAX) ) +
N'</table>'+
'
<tr>
<td class="contenido">
<font color="#003399" face="Verdana"><font size="2"><br><br>
any text..  any text,...
<a href="http://sitio.com" target="_blank">Clic now.</a>.
</font>
</td>
 </tr>
<tr><td>
<br><br>any text... any text...</td> </tr>
<tr>
<td class="contenido">
<br><br><br>any text,,any text
       
</td>
 </tr>
'

Exec  msdb.dbo.sp_send_dbmail      

@profile_name =  'correo',
@recipients = @mailcli,
@subject = 'Any text',
@body =  @tableHTML,
@body_format = 'HTML'




The email not send..  any help?  thanks.

Answer 1

require('conexion.php');   // this is the connection, it is fine
$params = array($id_cliente);  // $id_cliente it´s  any  var from .php
$stmt = sqlsrv_query($conn, 'SpSendMail',$params);

if ( $stmt )
{
    while ( ($rv=sqlsrv_next_result($stmt)) ) ;
}
else
{
    $rv = false;
}
if ( $rv===false )
{
    echo 'SQL Error: ' . print_r(sqlsrv_errors(),true);
}

---

Rob

Answer 2

Hi Robert.

when I run the procedure from the console works perfect  

Ej: Exec SpSendMail 95456789

The result it´s success

the procedure not run with I'm send the var from url.

The code  in the php is:

<a href="ver_gener.php?acciontotal=enviartodos&id_cliente=95456789">Procesar ahora</a>

<?php
 if($acciontotal == "enviartodos"){
  require('send_all.php');
  //require('updateenvio.php');
 }

the code the send_all.php is:

<?php
require('conexion.php');   // this is the connection, it is fine
$params = array($id_cliente);  // $id_cliente it´s  any  var from .php
sqlsrv_query($conn, 'SpSendMail',$params);
?>

Any Help?

---

Ohsalamanca

Answer 3

The result by your instrucctions int the file .php:

<?php
require('conexion.php');
echo "this is the variable " .$id_cliente."<br><br>";
$params = array($id_cliente);
echo "This is the variable from array";
print_r($params);
$stmt = sqlsrv_query($conn, 'spsendmail', $params);

if ( $stmt )
{
    while ( ($rv=sqlsrv_next_result($stmt)) ) ;
}
else
{
    $rv = false;
}
if ( $rv===false )
{
    echo 'SQL Error: ' . print_r(sqlsrv_errors(),true);
}

?>

The result is:

this is the variable 860022233    // this is the parameter by url

This is the variable of arreglo Array ([0] => 860,022,233)  this is the array

SQL Error: Array ([0] => Array ([0] => 42000 [SQLSTATE] => 42000 [1] => 201 [code] => 201 [2] => [Microsoft] [SQL Server Native Client 10.0] [SQL Server] procedure or function 'Spsendmail' waiting for the parameter '@ p_cliente', which was not specified. [message] => [Microsoft] [SQL Server Native Client 10.0] [SQL Server] procedure or function 'Spsendmail' waiting for the parameter '@ p_cliente', which was not specified.))

Any Help?

---

Ohsalamanca

Answer 4

Yes, sorry, change the 3rd line to this:

$stmt = sqlsrv_query($conn, '{call SpSendMail(?)}',$params);

---

Rob

Answer 5

Ok..  successfully

Thanks

---

Ohsalamanca

Answer 6

ohsalamanca,

Did Robert's answer fix your problem?

Thanks,

Jonathan

---

This posting is provided 'AS IS' with no warranties, and confers no rights.

Answer 7

Yes sr the solution was effective

thanks