BEGIN TRAN cause php sqlsrv return empty

[eluser]Unknown[/eluser]
This is strange, when I have the "begin tran" in the sp, it won't return any row, but if taken away, it works fine. Why is it so ? I am using codeigniter sqlsrv db driver.

this is a simplified sp , does not reflect the real one. But it does demonstrate the problem

Code:

ALTER proc [dbo].[sp_test] @MembershipId int, @CardNumber varchar(50) as

begin tran
select 'hello world' hi
commit tran

php

Code:

$sql = sprintf("EXEC [sp_test] @MembershipId=0,@CardNumber=123");      
$query =  $this->oDb->query($sql);
print_r($query->result());

[eluser]Unknown[/eluser]

To those who may face this issue, the solution is so simple.

DON'T USE EXEC in the query, so should be this

Code:

$sql = sprintf("[sp_test] @MembershipId=0,@CardNumber=123");