PHP Calling MS SQL Stored Procedures

Hi All,

I'm new to PHP and am banging my head against the wall in an effort to call an MS SQL stored procedure.  Any help would be appreciated!  I'm using currently the ADOdb abstraction layer.

This simple code should call a store procedure called procCountry_Upd.  This stored proc has two input paramters (@i32CountryID, and @strCountry).  If a value of @i32CountryID = 0 is passed, a record is added, however if an existing value is passed, then the record is updated.  The return value is always the record identity.

My current code is:

<?php
echo "about to connect<br>";
include('adodb/adodb.inc.php');
$db = &ADONewConnection("ado_mssql");      
print "<h1>Connecting DSN-less $db->databaseType...</h1>";                  
$myDSN="PROVIDER=MSDASQL;DRIVER={SQL Server};SERVER=myserver;DATABASE=mydatabase;UID=sa;PWD=;";
$db->Connect($myDSN);

$stmt = $db->PrepareSP("procCountries_Upd");
$db->InParameter($stmt,$CID,"i32CountryID");
$db->InParameter($stmt,$C,"strCountry");
$db->OutParameter($stmt,$NewID,"i32CountryIDNew");
$db->OutParameter($stmt,$ret,"RETVAL");

$CID = 0;
$C = "New Zealand";
$rs = $db->Execute($stmt);
if( !$rs ) {
print $db->ErrorMsg();
die;
}
echo "String = $ret\n";
echo $NewID

?>

The procedure works fine in Query Analyser, but when I execute the PHP page, all that happens is a blank record is inserted!!!!

The stored procedure code is:

ALTER PROCEDURE [dbo].[procCountries_Upd]
@i32CountryID AS INT = 0
, @strCountry AS NVARCHAR(50) = ''
, @i32CountryIDNew AS INT = 0 OUTPUT

AS
/**********************************************************************
Purpose :      Update/Insert for Countries tbl.
Notes   :      -
History  :      DJA      21-Apr-2006      Initial Creation.
**********************************************************************/
SET NOCOUNT ON
DECLARE @i32Error AS INT
DECLARE @i32TranCount AS INT

SET @i32Error = 0
SET @i32TranCount = @@TRANCOUNT

-- Update OR Insert ?
IF EXISTS (SELECT CountryID
            FROM Countries
            WHERE CountryID = @i32CountryID)
BEGIN
      UPDATE Countries
      SET Country = @strCountry
      WHERE CountryID = @i32CountryID
END
ELSE
BEGIN
      -- Insert.      
      
      INSERT INTO Countries
            (Country)
      VALUES (@strCountry)
END

SET @i32CountryIDNew = @@IDENTITY
RETURN @@IDENTITY