PHP写sqlserver事务,php调用sqlserver存储过程使用事务处理

第一次编写

sqlserver存储过程并在php里调用，把脑瓜都整大了，奋战了一天多最后还是搞定了。

实现功能：在

sqlserver里面实现事务处理，保证数据库操作安全；

接收sqlserver存储过程输出的各种状态的值。

sqlserver里面的存储过程如下：

ALTER PROC PK_Shop_Member

@nUserID INT,

@nMoney BIGINT,

@DATE DATETIME,

@nUserID2 INT,

@nResult INT OUTPUT//此变量装载存储过程输出的值

WITH ENCRYPTION AS

BEGIN

-- 属性设置

SET NOCOUNT ON

BEGIN TRY

BEGIN TRANSACTION T

UPDATE GaScoInfo SET Sco = Sco-@nMoney WHERE USER=@nUserID

UPDATE [GameUser].[dbo].[Accounts] SET Mem = '1',MemberOverD = @DATE WHERE USER=@nUserID2

COMMIT TRANSACTION T

SET @nResult=3

END TRY

BEGIN CATCH

ROLLBACK TRANSACTION T

SET @nResult=2

END CATCH

END

RETURN 0

GO

php里面调用如下：

$db_host='******';

$db_user='='******';

$db_pass='='******';

$db_name='='******';

$nResult= '99';//此处为定义接收存储过程输出的变量

$db_connect=mssql_connect($db_host,$db_user,$db_pass);

$db_select=mssql_select_db($db_name,$db_connect);

$stmt = mssql_init("PK_Shop_Member", $db_connect);

mssql_bind($stmt,"@nUserID",'111',SQLINT4,FALSE,FALSE,30);

mssql_bind($stmt,"@nMoney",'222',SQLFLT8,FALSE,FALSE,30);

mssql_bind($stmt,"@Date",'2012-4-2 20:10:56',SQLVARCHAR,FALSE,FALSE,30);

mssql_bind($stmt,"@nUserID2",'465',SQLINT4,FALSE,FALSE,30);

mssql_bind($stmt,"@nResult",$nResult,SQLINT4,TRUE,FALSE,30); //为存储过程添加一个输出参数

if($rs=mssql_execute($stmt)){

if($nResult=='99')exit('99');//网络异常

elseif($nResult=='2')exit('2');//'网络异常

elseif($nResult=='3')exit('1');//成功

}else{

exit('3');//'网络异常，交易失败'

}