Execute Store Procedure Using PHP

<?php
function &get_conn()
{
   global $conn;
   $myServer = "localhost";
   $myUser   = "markuser";
   $myPass   = "markpass";
   $myDB     = "markdb";

   if(!$conn)
   {
      $conn = mssql_connect($myServer, $myUser, $myPass)
         or die("Couldn't connect to SQL Server on $myServer");
      mssql_select_db($myDB, $conn)
         or die("Couldn't select database $myDB");
   }
   return $conn;
}

/* get a connection */
$conn =& get_conn();
?>

(Some of you may frown at the use of global $conn. I don’t normally use this, 
but it was a quick and dirty hack, not for production use….)

So far, all pretty standard stuff.

When executing a stored procedure, you have two choices.

1. (BAD) Execute it as a standard SQL statement:

EXEC myprocedure param1, param2, param3
or

EXEC myprocedure @id=param1, @name=param2, @email=param3
This is great from the command line when using isql, or when using SQL query analyzer (damn the Americans and their Z’s!).


2. (GOOD) A far more robust solution is to use the mssql_init, mssql_bind and 
mssql_execute methods, and bind named (and typed) parameters to your stored procedure object. 
The following snippet example gives you an idea of how to do this:

<?php
/* prepare the statement resource */
$stmt=mssql_init("myprocedure", $conn);

/* now bind the parameters to it */
mssql_bind($stmt, "@id",    $id,    SQLINT4,    FALSE);
mssql_bind($stmt, "@name",  $name,  SQLVARCHAR, FALSE);
mssql_bind($stmt, "@email", $email, SQLVARCHAR, FALSE);  

/* now execute the procedure */
$result = mssql_execute($stmt);
?>
There are two great benefits to using this approach:

Security - You don’t have to worry about escaping characters (like apostrophes) in your variable names. This makes nasty things like SQL injection impossible.
Typed variables - If you try and put text in a field that should be an integer you will get an error. While nobody likes errors, it’s much better to get 
them here than at the database end.
So that’s it in a nutshell! I needed my stored procedure to return stuff to me,
but I was having problems getting my output parameters to work. I’ve just seen the following in the online PHP documentation,
posted by a chap called Matt, that may help here:

1) you need to pass your php variable by reference. mssql_bind($stmt, "@outParam", &$outParam, true)

2) php-4.3.1-win32 had a binding bug and output parameters do not bind. 4.1 and 4.3.2rc1 do not have the bug. I have not tested other versions.

PHP Manual Reference :

Example #4 Calling a stored procedure with an output parameter
<?php
$stmt = $dbh->prepare("CALL sp_returns_string(?)");
$stmt->bindParam(1, $return_value, PDO::PARAM_STR, 4000);

// call the stored procedure
$stmt->execute();

print "procedure returned $return_value\n";
?>
Developers may also specify parameters that hold values both input and output; the syntax is similar to output parameters. In this next example, the string 'hello' is passed into the stored procedure, and when it returns, hello is replaced with the return value of the procedure.

Example #5 Calling a stored procedure with an input/output parameter
<?php
$stmt = $dbh->prepare("CALL sp_takes_string_returns_string(?)");
$value = 'hello';
$stmt->bindParam(1, $value, PDO::PARAM_STR|PDO::PARAM_INPUT_OUTPUT, 4000); 

// call the stored procedure
$stmt->execute();

print "procedure returned $value\n";
?>