MSSQL Stored Procedure has rows, but mssql_num_rows returns 0

Question

I'm having a really strange problem with some PHP and MSSQL that I'm working with. I'm working with stored procedures to retrieve information that a user has just stored into the database so that they can print out a record of their entry.

Initially I was having problems with getting the header details to display on the screen, so ended up changing the code to work by setting the boolean for $skip_results to true. This then meant that I had to call the stored procedure a second time without that setting to get the associated rows of information.

Here's what I did:

//populate header with order information - this one uses true in the mssql_execute in order to return all the output variables
/* prepare the statement resource */
$confirmationstmt=mssql_init("stored-procedure-name", $link);

/* now bind the parameters to it */
mssql_bind($confirmationstmt, "@var1",  $var1,  SQLVARCHAR, FALSE);
mssql_bind($confirmationstmt, "@var2",  $var2,  SQLFLT8, TRUE);
mssql_bind($confirmationstmt, "@var3",    $var3,    SQLVARCHAR,    TRUE);
mssql_bind($confirmationstmt, "@var4",    $var4,    SQLVARCHAR,    TRUE);
mssql_bind($confirmationstmt, "@var5",    $var5,    SQLVARCHAR,    TRUE);
mssql_bind($confirmationstmt, "@var6",    $var6,    SQLVARCHAR,    TRUE);
mssql_bind($confirmationstmt, "@var7",    $var7,    SQLVARCHAR,    TRUE);
mssql_bind($confirmationstmt, "@var8",    $var8,    SQLINT4,    TRUE);
mssql_bind($confirmationstmt, "@var9", $var9, SQLVARCHAR, true);
mssql_bind($confirmationstmt, "@var10", $var10, SQLFLT8, true);
mssql_bind($confirmationstmt, "@var11", $var11, SQLFLT8, true);
mssql_bind($confirmationstmt, "@var12", $var12, SQLFLT8, true);
mssql_bind($confirmationstmt, "@var13", $var13, SQLFLT8, true);

// now execute the procedure
$confirmationresult = mssql_execute($confirmationstmt, true);

//populate header with order information - this one uses true in the mssql_execute in order to return all the output variables
/* prepare the statement resource */
$numlinesstmt=mssql_init("stored-procedure-name", $link);

/* now bind the parameters to it */
mssql_bind($numlinesstmt, "@var1",  $var1,  SQLVARCHAR, FALSE);
mssql_bind($numlinesstmt, "@var2",  $var2,  SQLFLT8, TRUE);
mssql_bind($numlinesstmt, "@var3",    $var3,    SQLVARCHAR,    TRUE);
mssql_bind($numlinesstmt, "@var4",    $var4,    SQLVARCHAR,    TRUE);
mssql_bind($numlinesstmt, "@var5",    $var5,    SQLVARCHAR,    TRUE);
mssql_bind($numlinesstmt, "@var6",    $var6,    SQLVARCHAR,    TRUE);
mssql_bind($numlinesstmt, "@var7",    $var7,    SQLVARCHAR,    TRUE);
mssql_bind($numlinesstmt, "@var8",    $var8,    SQLINT4,    TRUE);
mssql_bind($numlinesstmt, "@var9", $var9, SQLVARCHAR, true);
mssql_bind($numlinesstmt, "@var10", $var10, SQLFLT8, true);
mssql_bind($numlinesstmt, "@var11", $var11, SQLFLT8, true);
mssql_bind($numlinesstmt, "@var12", $var12, SQLFLT8, true);
mssql_bind($numlinesstmt, "@var13", $var13, SQLFLT8, true);

// now execute the procedure
$numlinesresult = mssql_execute($numlinesstmt);

$numlines = mssql_num_rows($numlinesresult);

The variables in the first set of mssql_bind statements are all correctly populated, and while you would expect the second call to overwrite them they don't! When I first applied this 'fix' to the site the $numlines variable was being correctly populated which allowed me to then work through all the rows brought back by the select statement the stored procedure creates. However, 24 hours later the second set stopped working and I just get a 0 from the mssql_num_rows($numlinesresult) call.

Can anyone please advise on what I've done wrong here, because I really shouldn't have to call the stored procedure twice, each time in a different way, in order to get all the information out.

Thanks in advance!

Answer 1

Stored procedure contains SET NOCOUNT ON