Query/Stored Procedure question.

---------------------------------------------------------
I am trying to get the number of rows that a subquery returns inorder to perform paging (need to find out remainder of (total records / records per page) for displaying last page records).

The value for @INNERQUERY is dynamic SQL which returns all the rows that meet the criteria. I need to capture the total records inorder to perform my calculation.

I can not figure out why I am getting the error message. I believe I have all the bases covered, but obviously I am missing something. I read that when using OUTPUT that it is a 'text' value which is why I am assigning the count to @varRowsOUT.

Code with in the stored Proc:

declare @varRowCount as  varchar(4)
declare @ROWSQUERY as varchar(5000)
declare @Params as varchar(500)

set @ROWSQUERY = N'SELECT @varRowsOUT = count(*) FROM (' + @INNERQUERY + ') as SubQuery'
set @Params = N'@varRowsOUT varchar(4) OUTPUT'

      
Exec sp_executesql
      @ROWSQUERY,
      @Params,
      @varRowsOUT = @varRowCount OUTPUT;

select @varRowCount

Results in:
Msg 214, Level 16, State 2, Procedure sp_executesql, Line 267
Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.


I've already tried:
1)      Exec sp_executesql
      @statement = @ROWSQUERY,
      @Params = N'@varRowsOUT varchar(4) OUTPUT',
      @varRowsOUT = @varRowCount OUTPUT;

2) Read many examples from many sources (Please don't say "Read BoL")
3) Copy-Pasted an online example for Northwind Db and played with it trying to figure it out.


-----------------
Just to clarify, print(@ROWSQUERY) shows that the query is (which executes perfectly):

SELECT @varRowsOUT = count(*) FROM
      (select top 150      a.intShopControlNumber,      a.pkShopDrawingID
      from tblShopDrawing a
      left join tblBillOfMaterials bom on fkShopDrawingID = pkShopDrawingID
      left join tblMaterialCodeSize mcs on bom.fkMaterialCodeSizeID = pkMaterialCodeSizeID
      left join tblMaterialCode mc on mcs.fkMaterialCodeID = pkMaterialCodeID
      inner join tblDesignDocument b on pkDesignDocumentID = a.fkDesignDocumentID
      inner join tblDesignDocument c on c.pkDesignDocumentID = a.fkDesignDocumentID_Isometric
      inner join tblShopDrawingRevision d on d.fkShopDrawingID = a.pkShopDrawingID
      inner join tblDesignDocumentRevision e on e.pkDesignDocumentRevisionID = d.fkDesignDocumentRevisionID
      left join tblHoldHistory f on f.fkDesignDocumentID = b.pkDesignDocumentID
      left join tblHoldHistory g on g.fkDesignDocumentID = a.fkDesignDocumentID_Isometric
      left join tblHoldType h on h.pkHoldTypeID = f.fkHoldTypeID  
      where a.fkProjectID = 70
      and e.bolCurrent = 1  
      and c.varDesignDocumentNumber LIKE 'M2%'
      GROUP BY a.intShopControlNumber, a.pkShopDrawingID  
      ORDER BY intShopControlNumber ASC
      ) as SubQuery

Can anyone help me figure what it is I am missing?

Thanks in advance.
--------------------------------------------------------------------------------------------Start Free Trial