want to share with you that I had a hard time with mssql_execute on a platform that was running php using FastCGI. Well, I guess the problem was that mssql_init always returned null. So after investigating this, and plus the fact that I don't have total control over the platform, I gave up and used mssql_query instead.
Using the function below, it is possible to generate most stored procedure calls, with the added benefits that one doesn't have to worry to much about datatype mappings, and that the funtion return exception codes and error message. So here goes:
<?php
function _mssql_exec_sp($storedproc,$params) {
$varlist = "";
$setlist = "";
$parmlist = "";
$outs = "";
foreach ($params as $key => $value) {
$quote = strpos($value['type'],'char')!==false;
$varlist .= "@$key ".$value['type'].",\\n";
if (isset($value['value'])) {
$setlist .= "set @".$key."=".($quote?"'":'').$value['value'].($quote?"'\\n":"\\n");
}
$paramlist .= " @".$key.(isset($value['out'])?' output,':',');
if (isset($value['out'])) {
$outs .= "@$key as $key,";
}
}
if (strlen($paramlist)) {
$paramlist = substr($paramlist,0,strlen($paramlist)-1);
}
$stmt = "begin try\\n";
$stmt .= "declare\\n";
$stmt .= "@ret int";
if (strlen($varlist)) {
$stmt .= ",\\n";
$stmt .= $varlist;
$stmt = substr($stmt,0,strlen($stmt)-2);
}
else {
$stmt .= "\\n";
}
$outs = "@ret as ret,".$outs;
$outs = substr($outs,0,strlen($outs)-1);
$stmt .= "\\n".$setlist;
$stmt .= "exec @ret = ".$storedproc.$paramlist."\\n";
$stmt .= "select ".$outs."\\n";
$stmt .= "end try\\n";
$stmt .= "begin catch\\n";
$stmt .= "select error_number() as ret,error_message() as errorMsg\\n";
$stmt .= "end catch\\n";
return mssql_query($stmt);
}
?>
example call:
<?php
$params = array(
'socialid' => array(
'type' => 'char(10)',
'value' => $sid
),
'cust_name' => array(
'type' => 'varchar(20)',
'value' => 'our name'
),
'id' => array(
'type' => 'int',
'out' => true
),
);
$result = _mssql_exec_sp('sp_register_cust', $params);
$row = mssql_fetch_array($result);
?>