存储过程和sqlsrv_num_rows - 游标错误？

Question

Is there a way to use sqlsrv_num_rows() with stored procedures?

Basically when i try to use this function I receive one of these errors, depending on the cursor i supply:

Array ( [0] => Array ( [0] => IMSSP [SQLSTATE] => IMSSP [1] => -50 [code] => -50 [2] => This function only works with statements that have static or keyset scrollable cursors. [message] => This function only works with statements that have static or keyset scrollable cursors. ) ) 

or

Array ( [0] => Array ( [0] => 01000 [SQLSTATE] => 01000 [1] => 16954 [code] => 16954 [2] => [Microsoft][SQL Server Native Client 10.0][SQL Server]Executing SQL directly; no cursor. [message] => [Microsoft][SQL Server Native Client 10.0][SQL Server]Executing SQL directly; no cursor. ) [1] => Array ( [0] => 01S02 [SQLSTATE] => 01S02 [1] => 0 [code] => 0 [2] => [Microsoft][SQL Server Native Client 10.0]Cursor type changed [message] => [Microsoft][SQL Server Native Client 10.0]Cursor type changed ) )

I've spent a bit of time trying to search for a solution to this issue, but no luck.

How can i get the row count from a stored procedure using SqlSrv? Would i have to manually loop through the results and $count++; I would like to avoid that. Or, is there some way I could change the actual stored procedure to include the cursor? I'm not very familiar with cursors, so I don't really know if that is a solution or not.

Thank you in advance!

Answer 1

see this: sqlsrv_num_rows() with stored procedures doesn't seem like there is much other than work arounds.

you could capture the @@ROWCOUNT within the procedure and return it as an OUTPUT parameter.

CREATE PROCEDURE YourProc
(
    @param1 int
   ,@param2 varchar(5)
   ,@rowCountOf int OUTPUT
)
AS

SELECT * FROM TABLE WHERE ...  --returns result set
SET @rowCountOf=@@ROWCOUNT     --set output variable to be the row count
return 0
GO

or you could use sp_executesql instead as described in the above link.