Stored Procedure Writer v2.0

spwriter.php

<pre>
<?PHP
$sqlserver = '';
$username = '';
$password = '';
$database = "";
/*
 * writer.php version 2.0 - Major update
 * Takes stored procedure name, the input variables and an array of output variables
 * outputs php code necessary for getting results using php pdo
 * 
 */

//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~\\

$procedureName = 'stored_procedure_name';

//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~\\

$connectionInfo = array( "Database"=>$database, "UID"=>$username, "PWD"=>$password);
$conn = sqlsrv_connect( $sqlserver, $connectionInfo);
if( $conn ) {
  //echo "Connection established.<br />";
}else{
  echo "Connection could not be established.<br />";
  die( print_r( sqlsrv_errors(), true));
}

//construct the query.
$query = "SELECT name, type_name(user_type_id) AS type, max_length, is_output FROM sys.parameters WHERE object_id = object_id('DBO.".$procedureName."')";
$stmt = sqlsrv_query( $conn, $query);
if( $stmt === false ) {
  die( print_r( sqlsrv_errors(), true));
}
// Make the first (and in this case, only) row of the result set available for reading.

$inputs = [];
$outputs = [];

while($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)){
  if($row['is_output']==1){
    if($row['type']=='varchar'){
      $type = 'varchar('.$row['max_length'].')';
    } else {
      $type = $row['type'];
    }
    $outputs[$row['name']] = $type;
  } else {
    $inputs[] = $row['name'];
  }
}

$i = 1;
$array = $inputs;
$input = implode(', ', $inputs);

//nospace on the first line because it makes it easier to copy and paste ;)
print 'if(!isset($pdo) || $pdo==null){'."\r\n";
print '      $pdo = DB::connection()->getPdo();'."\r\n";
print '    }'."\r\n";
print '    //SET NOCOUNT ON; --you might need this... '."\r\n";
print '    $stmt = $pdo->prepare("DECLARE @return int';
if(count($outputs)>0){
  foreach($outputs as $var=>$type){
    print ', '.$var.' '.$type;
  }
}
print '; EXEC @return = ['.$procedureName.'] ';
print implode(' = ?, ', $array);
$comma = '';
if(count($array)>0 && strlen($input)>0){
  print ' = ?';
  $comma = ', ';
}
if(count($outputs)>0){
  foreach($outputs as $var=>$type){
    print $comma.$var.' = '.$var.' OUTPUT';
    $comma = ', ';
  }
}
print '; SELECT @return AS N\'@returnVal\'';
if(count($outputs)>0){
  foreach($outputs as $var=>$type){
    print ', '.$var.' AS N\''.$var.'\'';
  }
}
print ';");'."\r\n";
if(count($array)>0 && strlen($input)>0){
  foreach($array as $item){
    print'      $stmt->bindValue('.$i.', $'.str_replace('@p_', '', str_replace('@', '', $item)).' ); //'.str_replace('_', ' ', str_replace('@p_', '', $item))."\r\n";
    $i++;
  }
}
print '    $stmt->execute();'."\r\n";
//print '    //TODO: Format response'."\r\n";
print '    //Should have the resultset...'."\r\n";
print '    $rows = $stmt->fetchAll( \PDO::FETCH_ASSOC );'."\r\n";
print '    $stmt->nextRowSet();'."\r\n";
print '    //Should have the select response, or if there are multiple resultsets, it\'ll be here.'."\r\n";
print '    $return = $stmt->fetchAll( \PDO::FETCH_ASSOC );';

/*
  for returned variables:
  DECLARE @var type; SET @var = null; STATEMENT; SELECT @var AS N'var';
                                          ^-- And set the output var like this: @p_Service_Request_ID = @var OUTPUT
 */

Some php I wrote for the ease of writing stored procedures. Might not work perfectly but it does what I need it to do. Having had to write code for > 100 different stored procedures, this has come in very handy. Now, it smartly querys the database for the input and output variable names! V1 I had to manually define them all. Enjoy!