MSSQL and DB, Zend as PHP Framework,

I am using this way to call SP with I/P Parameters and to get O/p Parameters.

It seems I am writing SQL code in PHP.

Any other good approaches?

 $str1 = "DECLARE @Msgvar   varchar(100); DECLARE @last_id int;

exec DispatchProduct_m_Ins $DispatchChallanId,'$FRUNo',$QTY,$Rate,$Amount,
".$this->cmpId.",".$this->aspId.",".$this->usrId.",@Msg = @Msgvar OUTPUT,@LAST_ID = @last_id OUTPUT;

SELECT  @Msgvar AS N'@Msg',@last_id AS '@LAST_ID'; ";//Calling SP

$stmt = $db->prepare($str1);

$rsDispProd = $stmt->fetchAll();

$DispatchProductId   = $rsDispProd[0]["@LAST_ID"];//get last ins ID as O/p Parameter    
  • 1
    What's wrong with what you are already doing?
    – yannis
    Commented Sep 15, 2012 at 5:34
  • it seems i m writing SQL code in PHP.... i don't want that ! Commented Sep 15, 2012 at 5:37
  • @gnat What you have Edited ? Commented Sep 15, 2012 at 6:01
  • 1
    @BhavinRana: click on the "edit x time ago" link above the editor's avatar, you'll see exactly what was changed.
    – Mat
    Commented Sep 15, 2012 at 6:40

1 Answer 1


Unfortunately I am not using the Zend Framework but I did good experiences using a dedicated class for the stored procedure stuff which handles the parameters, do the queries and is dealing with the results.

My Model extends the class SPData and adds the methods according to the given SPs. PHP PDO with MS SQL Drivers are used to call the available stored procedures.

Example using a SP to get an array as a result:

public function getTransactionList($intLimit=0){
    $intUserId = $this->getCurrentUserId();
    if (false == $intUserId) return false;
    $strStatement = '{? = call web.usp_GetTransactionList(?,?)}';
    $this->setParam(null, PDO::PARAM_INT | PDO::PARAM_INPUT_OUTPUT, 4);
    $arr = $this->doCall($strStatement);
    return $arr;

Example using a SP to get a speciefed output parameter as a result:

public function getBalance(){
    $intUserId = $this->getCurrentUserId();
    if (false == $intUserId) return false;
    $strStatement = '{? = call web.usp_GetBalance(?,?)}';
    $this->setParam(null, PDO::PARAM_INT | PDO::PARAM_INPUT_OUTPUT, 4);
    $this->setParam(null, PDO::PARAM_STR | PDO::PARAM_INPUT_OUTPUT, 30);
    $arrParam = $this->getParam(3);
    return (float)$arrParam['value'];

Source code of the SPData Class:

class SPData {

protected $con=false;
protected $arrParams=array();
protected $arrParamsBackup=array();
protected $intColCount = 0;
protected $rs=array();

protected function getConnection() {
    if (false == isset($GLOBALS['dbcon']))
        try {
            $GLOBALS['dbcon'] = new PDO('sqlsrv:Server='.DB_SERVER.';database='.DB_DB, DB_USER, DB_PASS, array('ConnectionPooling' => 0));
        } catch (PDOException $e) {
            die('Connection failed: '.$e->getMessage());
    return $GLOBALS['dbcon'];

protected function resetParams(){

protected function backupParams(){
    $this->arrParamsBackup = $this->arrParams;

public function setParam($strValue, $intParams=PDO::PARAM_INT, $intLength=null){
    $intIdx = count($this->arrParams);

    if ($intLength == null)
        $this->arrParams[$intIdx] = array('value'=>$strValue, 'params'=>$intParams);
        $this->arrParams[$intIdx] = array('value'=>$strValue, 'params'=>$intParams, 'length'=>$intLength);

public function getParam($intIdx){ // from backup array!
    return $this->arrParamsBackup[$intIdx];

public function doCall($strStatement){

    if (false == $this->con) $this->con = $this->getConnection();

    $stmt = $this->con->prepare($strStatement);
    foreach ($this->arrParams as $key => $value){
        if (isset($value['length'])) $stmt->bindParam($key, $this->arrParams[$key]['value'], $this->arrParams[$key]['params'],$this->arrParams[$key]['length']);
        else $stmt->bindParam($key, $this->arrParams[$key]['value'], $this->arrParams[$key]['params']);

    $arrReturn = $stmt->fetchAll(PDO::FETCH_ASSOC);
    $this->intColCount = count($arrReturn);


    $this->backupParams(); // backup for getting output params

    return $arrReturn;

public function getColCount(){
    return $this->intColCount;


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.