CODEWITHSUNDEEP
phpsql-server
navyajapani
navyajapani

Reputation: 21

sqlsrv_num_rows not working for Stored procedures

I m using PHP-SQRSRV driver to read my MSSQL database.

When I use sqlsrv_num_rows, i get 0 results as cursor can not be set properly. This happens just in case of Stored Procedures. I have used SQLSRV_CURSOR_STATIC & SQLSRV_CURSOR_KEYSET both. None worked.

Initially I got this error : Executing SQL directly, no cursor.. Executing SQL directly; no cursor in PHP when calling a stored procedure

But then after setting [sqlsrv_configure("WarningsReturnAsErrors", 0);] that was resolved. Now all I m getting is 0 or -1 as output.

I want just counts of the records, not the whole record. And I have to use a stored procedure.

Please help me find a solution to this.

Upvotes: 0

Views: 1350

Answers (1)

Ed Harper
Ed Harper

Reputation: 21495

Stored procedures do not return an implcit row count.

You will need to amend your stored procedure to generate row count(s) and return them either as OUTPUT parameters from the procedure or as an additional result-set.

How you do this will depend on the nature of your procedure and what you're trying to count. If you are counting rows which are being affected by one or more SELECT, INSERT or UPDATE statements within the procedure, you can use the @@ROWCOUNT internal parameter to set the value of an OUTPUT parameter:

<some SQL operation>    
SET @rowcount = @@ROWCOUNT

If you're simply counting rows in tables, you can assign the output of a simple count query to a parameter:

SELECT @rowcount = COUNT(1) FROM <some table>

Upvotes: 1

Related Questions