PHP MSSQL Stored Procedure with Parameters (In and Out)

[agrarian]

Any could give me a PHP script on how to call or execute a MS SQL Server stored procedure, passing parameters to it and receiving a value from it using an Output parameter?

[silviuks]

// execute SQL Server store procedure
function MsExecuteProcedure($procedure)
{
    $proc = mssql_init($this->_db."..".$procedure);
    $rez = mssql_execute($proc) or die(mssql_get_last_message());
    return $rez;
}

// function that adds a parameter to a SQL Server stored procedure
function MsAddParameterToProcedure($procedure, $msParamName, $paramValue, $type, $output = null)
{
    if(!$output)
        mssql_bind($procedure, $msParamName, $paramValue, $type);
    else 
        mssql_bind($procedure, $msParamName, $paramValue, $type, true, false, 40);
}

// add parametter to a procedure
MsAddParameterToProcedure($proc, "@username", $username, SQLVARCHAR);

// execute procedure
$rez = MsExecuteProcedure($proc);

// get values 
$arr = mssql_fetch_assoc($rez);

Now ... i tried to build a website combining those two technologies but it was very (and i mean verrrryyy) slow. I don't know if was my hardware configuration or ....

anyway, i hope it will be useful what i just write ...
good luck

[agrarian]

I gave up trying to do the output code version of the Stored Procedure call, instead I changed my Stored Procedure to return a single row which would contain the information I needed.

Then, to call the MS SQL Server 2000 Stored Procedure from PHP, I did the following:

// Connect to SQL Server and check for errors
$conn = mssql_connect($db_host,$db_user,$db_password);
if ($conn===false)
{
	echo '<p>Cannot connect to SQL Server Database. Please try again later.</p>';
	exit;
}
if (mssql_select_db("MyDatabase",$conn) === false) 
{
	echo '<p>Cannot connect to MyDatabase. Please try again later.</p>';
	exit;
}
$proc = mssql_init('MyStoredProcedure',$conn);
mssql_bind($proc,'@ParameterOne',$ParameterOne,SQLVARCHAR);
mssql_bind($proc,'@ParameterTwo',$ParameterTwo,SQLVARCHAR);
mssql_bind($proc,'@ParameterThree',$ParameterThree,SQLVARCHAR);
if ($result = mssql_execute($proc)) 
{
	if ($row = mssql_fetch_row($result)) 
	{
		// now you can deal with the $row array to check out your results
	}
}

[99developer]

hi, i have done this through Adodb Library in very simple manner...

$addProduct = $obj->ExecuteQuery("Begin;DECLARE @ProductCode as varchar (100) ;EXEC CREATEPRODUCT'$pname', '$price', @ProductCode OUTPUT, '$merchantId';select @ProductCode;End;");

$productCode = $addProduct[0][0];

for more explanation you can visit this site..
[snipped]