Is there a simple way of making sqlsrv_num_rows() to work with stored procedures?

Question

Is there a way to use sqlsrv_num_rows() with stored procedures?
When a cursor (ie SQLSRV_CURSOR_KEYSET or STATIC) is set in the db connection options, as is required, the following notice is returned without a num_row result:

    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. ) )


Obviously, I'm great at neither PHP nor at T-SQL so really any help will do. So far I've come across three options which I wasn't too keen on trying, either because they aren't quite efficient or I lack the know-how / control of the SQL database:

    using a "count()" function
    passing a parameter from containing row count through the SP;
    creating a VIEW for the results of the SP, then applying sqlsrv_num_rows() on this VIEW

Are these my only options or is there some hidden gem which I'm yet to discover?

Answer 1

Immanuel-

Can you post the PHP code (and perhaps your stored procedure definition)? I'm not able to reproduce the error you are seeing. I find that this code (executed against the Northwind sample database) works as expected:

$sql = "{call GetOrdersByID(?)}";
$stmt = sqlsrv_query($conn, $sql, array('ALFKI'), array('Scrollable' => SQLSRV_CURSOR_KEYSET));
$row_count = sqlsrv_num_rows($stmt);
echo "Row count: $row_count";

Thanks.

-Brian

---

This posting is provided "AS IS" with no warranties, and confers no rights. http://blogs.msdn.com/brian_swan

Answer 2

Hi Brian,

Thanks for the prompt response.

I believe I'd have to modify the SP (say DECLARE a cursor), but I'm not exactly sure the proper way to do so. So, Ill start by posting that SP first:

 

USE [MTIS]
GO
/****** Object: StoredProcedure [dbo].[lst_Projects] Script Date: 08/05/2011 12:58:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROC [dbo].[lst_Projects]
   @ProjectID BIGINT = 0,
   @InActive int = 0,
   @ContactID bigint = 0
 
AS
 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
 SET NOCOUNT ON

 DECLARE @EntityID bigint
 DECLARE @CommentCount int

 --SET @EntityID = (dbo.FN_EntityTreeID (@ProjectID, 1))

 --SET @CommentCount = (SELECT COUNT([MemoID]) FROM [Log_ProjectMemos]
 --WHERE [Log_ProjectMemos].[EntityTreeID] = vw_EntityTreeProjects.EntityTreeID)

 --Create a temporary table of Memo Count Parameters

  SELECT [EntityTreeID], COUNT([MemoID]) as [MemoCount] INTO #TblMemoCount FROM [vw_ProjectMemos]
  GROUP BY [EntityTreeID]

IF @ContactID <> 0
 BEGIN

DECLARE @TblProjIDS TABLE
 (IDS bigint)

INSERT INTO @TblProjIDS
(IDs)

SELECT [ProjectID] FROM [vw_ContactIDProjects] WHERE [ContactID] = @ContactID AND ([Data] IS NULL OR DATA = 0)

  SELECT ('PROJ #: ' + CAST(vw_EntityTreeProjects.ProjectCode AS CHAR(10)) + ' : NAME = ' +
        CAST(vw_EntityTreeProjects.ProjectName AS CHAR(50))+ ' : PROPOSAL = ' +
        CAST(vw_EntityTreeProjects.ProposalNo AS CHAR(12))) AS
         ProjectInfo,
    #TblMemoCount.MemoCount,
    vw_EntityTreeProjects.ProjectName,
    vw_EntityTreeProjects.ProjectCode,
    ...
    vw_EntityTreeProjects.EntityTreeID,
    vw_EntityTreeProjects.EntityTypeID
    FROM vw_EntityTreeProjects INNER JOIN #TblMemoCount ON
    vw_EntityTreeProjects.EntityTreeID = #TblMemoCount.EntityTreeID
    WHERE vw_EntityTreeProjects.ProjectID IN (SELECT [IDs] FROM @TblProjIDS) AND [InActive] = @InActive
    ORDER BY vw_EntityTreeProjects.ProjectEntityLabelInteger,
    vw_EntityTreeProjects.ProjectEntityLabelString

  RETURN
 END
 
 

IF @ProjectID = 0

BEGIN
 SELECT ('PROJ #: ' + CAST(vw_EntityTreeProjects.ProjectCode AS CHAR(10)) + ' : NAME = ' +
        CAST(vw_EntityTreeProjects.ProjectName AS CHAR(50))+ ' : PROPOSAL = ' +
        CAST(vw_EntityTreeProjects.ProposalNo AS CHAR(12))) AS
         ProjectInfo,
 #TblMemoCount.MemoCount,
 vw_EntityTreeProjects.ProjectName,
 vw_EntityTreeProjects.ProjectCode,
 ...
 vw_EntityTreeProjects.EntityTreeID,
 vw_EntityTreeProjects.EntityTypeID
 FROM vw_EntityTreeProjects INNER JOIN #TblMemoCount ON
 vw_EntityTreeProjects.EntityTreeID = #TblMemoCount.EntityTreeID
 WHERE [InActive] = @InActive
 ORDER BY vw_EntityTreeProjects.ProjectEntityLabelInteger,
    vw_EntityTreeProjects.ProjectEntityLabelString
END

ELSE

BEGIN

IF @InActive >= 0


BEGIN
 SELECT ('PROJ #: ' + CAST(vw_EntityTreeProjects.ProjectCode AS CHAR(10)) + ' : NAME = ' +
        CAST(vw_EntityTreeProjects.ProjectName AS CHAR(50))+ ' : PROPOSAL = ' +
        CAST(vw_EntityTreeProjects.ProposalNo AS CHAR(12))) AS
         ProjectInfo,
 #TblMemoCount.MemoCount,
 vw_EntityTreeProjects.ProjectName,
 vw_EntityTreeProjects.ProjectCode,
 ...
  vw_EntityTreeProjects.EntityTreeID,
 vw_EntityTreeProjects.EntityTypeID
 FROM vw_EntityTreeProjects INNER JOIN #TblMemoCount ON
 vw_EntityTreeProjects.EntityTreeID = #TblMemoCount.EntityTreeID
 WHERE vw_EntityTreeProjects.ProjectID = @ProjectID AND [InActive] = @InActive
 ORDER BY vw_EntityTreeProjects.ProjectEntityLabelInteger,
    vw_EntityTreeProjects.ProjectEntityLabelString
END

ELSE

BEGIN
 SELECT ('PROJ #: ' + CAST(vw_EntityTreeProjects.ProjectCode AS CHAR(10)) + ' : NAME = ' +
        CAST(vw_EntityTreeProjects.ProjectName AS CHAR(50))+ ' : PROPOSAL = ' +
        CAST(vw_EntityTreeProjects.ProposalNo AS CHAR(12))) AS
         ProjectInfo,
 #TblMemoCount.MemoCount,
 vw_EntityTreeProjects.ProjectName,
 vw_EntityTreeProjects.ProjectCode,
 ...
 vw_EntityTreeProjects.EntityTreeID,
 vw_EntityTreeProjects.EntityTypeID
 FROM vw_EntityTreeProjects INNER JOIN #TblMemoCount ON
 vw_EntityTreeProjects.EntityTreeID = #TblMemoCount.EntityTreeID
 WHERE vw_EntityTreeProjects.ProjectID = @ProjectID
 ORDER BY vw_EntityTreeProjects.ProjectEntityLabelInteger,
    vw_EntityTreeProjects.ProjectEntityLabelString
END

END
 DROP TABLE #TblMemoCount

SELECT [NoCopySuppliers] FROM [LOG_EntityProjects] WHERE [ProjectID] = @ProjectID

 

I'm sure you won't need all that T-SQL, but thought I show it all since I didn't create it nor do I ABSOLUTELY understand it.

So basically a user logs in using a web form. Once authenticated, the user's id is used to generate a list of "projects" appended to that id

Here is the PHP code calling the list:

 

 $result = sqlsrv_query($conn, "{call Lst_Projects(,,?)}", array(&$cid), array( "Scrollable" => SQLSRV_CURSOR_STATIC ))
  or die( print_r( sqlsrv_errors(), true));
 $res_count = sqlsrv_num_rows($result);

Thanks!

 

Answer 3

When you execute a stored procedure that returns a result, you cannot use a scrollable cursor.

The statement runs on the server, then the driver returns false.

Version 3.0 CTP has a buffered query option, which looks useful, and may give some limited possibilities - it will always return the number of records in the result, but it has a size limit - often we need to use scrollable results with large data sets.  This might be the best option for stored procedures.

I haven't managed to get driver version 3.0 CTP installed on my computer yet, I don't know how well it works.

 

I suggest evaluating and returning the number of rows separately, for example:

With myQuery as (select A,B,C,D from myTable)
select *, ct.[rows] from myQuery
cross join (select count(*) [rows] from myQuery) as [ct];

Your result will have a column at the end of each row with the total number of rows in the query, and hopefully the server will only evaluate the query once.

This is just an idea that might give you some ideas about your own solution - you would need to modify your stored procedure to return the number of rows as a result set, or append a count column similar to this example above.

---

Rob

Answer 4

So if I can't use a scrollable cursor with a result set from a stored procedure, then how am I supposed to get the number of rows, since your sqlsrv_num_rows automatically returns false with a forward cursor. In the year 2012, there is no reason to NOT have a usable num_rows function for stored procedures. It worked fine before PHP abandoned the mssql functions for PHP 5.3.

Answer 5

Hi Rickochetp,

You can get the number of rows if you used a Buffered Query (but you can run into the size limit).

Additionally, you can also query the server for @@ROWCOUNT in order to get the number of rows from the previous query:
http://technet.microsoft.com/en-us/library/ms187316.aspx
http://stackoverflow.com/questions/243782/need-a-row-count-after-select-statement-whats-the-optimal-sql-approach

Cheers,

Jonathan

---

This posting is provided 'AS IS' with no warranties, and confers no rights.