How to determine ROWCOUNT without executing the select statement

I'm selecting data from a large table in a paged manner by using the
ROW_NUMBER ranking function in a function like the one shown below... I
want the function to also return the total number of rows in the dataset
AFTER the primary select is issued but before the paged data is selected
out. In other words, if there are 10,000 rows, and 3500 of them get
selected by the where clause, but the paging only returns 101 .. 200, I
want the total rows output to be set to 3500.
The way this is written currently, I use @.@.ROWCOUNT, but only get the
page size (eg 100). Is there an easy way to get the primary data set
size without resorting to memory or temporary tables, and without
executing the main query twice'
-mdb
#############################
PROCEDURE [dbo].[GetTablePagedAndSorted]
(
@.tableName nvarchar(100),
@.columnList varchar(2000),
@.sortExpression nvarchar(100),
@.whereClause varchar(2000),
@.startRowIndex int,
@.maximumRows int,
@.totalRows int OUTPUT
) AS
IF (LEN(@.whereClause) = 0) SET @.whereClause = '1=1'
-- Issue query
DECLARE @.sql nvarchar(4000)
SET @.sql = 'SELECT ' + @.columnList + ',RowRank '
SET @.sql = @.sql + ' FROM (
SELECT ' + @.columnList + ', ROW_NUMBER() OVER (ORDER BY ' +
@.sortExpression + ') AS RowRank
FROM ' + @.tableName + '
WHERE (' + @.whereClause + ')
) AS TableWithRowNumbers '
IF (@.maximumRows > 0)
BEGIN
SET @.sql = @.sql + '
WHERE RowRank >= ' + CONVERT(nvarchar(10), @.startRowIndex) +
'
AND RowRank < (' + CONVERT(nvarchar(10), @.startRowIndex) +
' + ' + CONVERT(nvarchar(10), @.maximumRows) + ')'
END
-- Execute the SQL query
PRINT @.sql
EXEC sp_executesql @.sql
SET @.totalRows = @.@.ROWCOUNT
######################################You could put the result into a temp. table. Or you could create a SELECT
COUNT(*) to get the count before you do the actual SELECT. I guess the
question is how important is it to know the intermediate result set size -
is it worth the extra inefficiency?
"Michael Bray" <mbrayATctiusaDOTcom@.you.figure.it.out.com> wrote in message
news:Xns99E1B6650917Embrayctiusacom@.207.46.248.16...
> I'm selecting data from a large table in a paged manner by using the
> ROW_NUMBER ranking function in a function like the one shown below... I
> want the function to also return the total number of rows in the dataset
> AFTER the primary select is issued but before the paged data is selected
> out. In other words, if there are 10,000 rows, and 3500 of them get
> selected by the where clause, but the paging only returns 101 .. 200, I
> want the total rows output to be set to 3500.
> The way this is written currently, I use @.@.ROWCOUNT, but only get the
> page size (eg 100). Is there an easy way to get the primary data set
> size without resorting to memory or temporary tables, and without
> executing the main query twice'
> -mdb
> #############################
> PROCEDURE [dbo].[GetTablePagedAndSorted]
> (
> @.tableName nvarchar(100),
> @.columnList varchar(2000),
> @.sortExpression nvarchar(100),
> @.whereClause varchar(2000),
> @.startRowIndex int,
> @.maximumRows int,
> @.totalRows int OUTPUT
> ) AS
> IF (LEN(@.whereClause) = 0) SET @.whereClause = '1=1'
> -- Issue query
> DECLARE @.sql nvarchar(4000)
> SET @.sql = 'SELECT ' + @.columnList + ',RowRank '
> SET @.sql = @.sql + ' FROM (
> SELECT ' + @.columnList + ', ROW_NUMBER() OVER (ORDER BY ' +
> @.sortExpression + ') AS RowRank
> FROM ' + @.tableName + '
> WHERE (' + @.whereClause + ')
> ) AS TableWithRowNumbers '
> IF (@.maximumRows > 0)
> BEGIN
> SET @.sql = @.sql + '
> WHERE RowRank >= ' + CONVERT(nvarchar(10), @.startRowIndex) +
> '
> AND RowRank < (' + CONVERT(nvarchar(10), @.startRowIndex) +
> ' + ' + CONVERT(nvarchar(10), @.maximumRows) + ')'
> END
> -- Execute the SQL query
> PRINT @.sql
> EXEC sp_executesql @.sql
> SET @.totalRows = @.@.ROWCOUNT
> ######################################
>|||"Mike C#" <xyz@.xyz.com> wrote in
news:OG9RqcZIIHA.4584@.TK2MSFTNGP03.phx.gbl:
> You could put the result into a temp. table. Or you could create a
> SELECT COUNT(*) to get the count before you do the actual SELECT. I
> guess the question is how important is it to know the intermediate
> result set size - is it worth the extra inefficiency?
> "Michael Bray" <mbrayATctiusaDOTcom@.you.figure.it.out.com> wrote in
> message news:Xns99E1B6650917Embrayctiusacom@.207.46.248.16...
>> I'm selecting data from a large table in a paged manner by using the
>> ROW_NUMBER ranking function in a function like the one shown below...
>> I want the function to also return the total number of rows in the
>> dataset AFTER the primary select is issued but before the paged data
>> is selected out. In other words, if there are 10,000 rows, and 3500
>> of them get selected by the where clause, but the paging only returns
>> 101 .. 200, I want the total rows output to be set to 3500.
Thanks, but as I said I do not want to use temporary tables, as this will
absolutely swamp my tempdb due to the potential size of the query results.
I considered your other suggestion previously, but then the question is how
do I get the results of an EXEC into a variable? Keep in mind that I have
to build the sql dynamically, and thus require sp_executesql. The
following syntax doesn't work:
SET @.totalRows = (EXEC sp_executesql @.mySqlStatement) ' doesn't parse
If I can find the solution to how to set a variable based on a dynamic sql
statement then I will be set.
-mdb|||Michael Bray <mbrayATctiusaDOTcom@.you.figure.it.out.com> wrote in
news:Xns99E2544576DFAmbrayctiusacom@.207.46.248.16:
> Thanks, but as I said I do not want to use temporary tables, as this
> will absolutely swamp my tempdb due to the potential size of the query
> results. I considered your other suggestion previously, but then the
> question is how do I get the results of an EXEC into a variable? Keep
> in mind that I have to build the sql dynamically, and thus require
> sp_executesql. The following syntax doesn't work:
> SET @.totalRows = (EXEC sp_executesql @.mySqlStatement) ' doesn't parse
> If I can find the solution to how to set a variable based on a dynamic
> sql statement then I will be set.
>
OK I found the solution - sp_executesql can actually accept input and
output parameters!!
See the wonderful article at:
http://www.sommarskog.se/dynamic_sql.html
-mdb|||Oops, I see you already found that
"Michael Bray" <mbrayATctiusaDOTcom@.you.figure.it.out.com> wrote in message
news:Xns99E26438875B7mbrayctiusacom@.207.46.248.16...
> Michael Bray <mbrayATctiusaDOTcom@.you.figure.it.out.com> wrote in
> news:Xns99E2544576DFAmbrayctiusacom@.207.46.248.16:
>> Thanks, but as I said I do not want to use temporary tables, as this
>> will absolutely swamp my tempdb due to the potential size of the query
>> results. I considered your other suggestion previously, but then the
>> question is how do I get the results of an EXEC into a variable? Keep
>> in mind that I have to build the sql dynamically, and thus require
>> sp_executesql. The following syntax doesn't work:
>> SET @.totalRows = (EXEC sp_executesql @.mySqlStatement) ' doesn't parse
>> If I can find the solution to how to set a variable based on a dynamic
>> sql statement then I will be set.
> OK I found the solution - sp_executesql can actually accept input and
> output parameters!!
> See the wonderful article at:
> http://www.sommarskog.se/dynamic_sql.html
> -mdb|||sp_executesql does take output params
"Michael Bray" <mbrayATctiusaDOTcom@.you.figure.it.out.com> wrote in message
news:Xns99E2544576DFAmbrayctiusacom@.207.46.248.16...
> "Mike C#" <xyz@.xyz.com> wrote in
> news:OG9RqcZIIHA.4584@.TK2MSFTNGP03.phx.gbl:
>> You could put the result into a temp. table. Or you could create a
>> SELECT COUNT(*) to get the count before you do the actual SELECT. I
>> guess the question is how important is it to know the intermediate
>> result set size - is it worth the extra inefficiency?
>> "Michael Bray" <mbrayATctiusaDOTcom@.you.figure.it.out.com> wrote in
>> message news:Xns99E1B6650917Embrayctiusacom@.207.46.248.16...
>> I'm selecting data from a large table in a paged manner by using the
>> ROW_NUMBER ranking function in a function like the one shown below...
>> I want the function to also return the total number of rows in the
>> dataset AFTER the primary select is issued but before the paged data
>> is selected out. In other words, if there are 10,000 rows, and 3500
>> of them get selected by the where clause, but the paging only returns
>> 101 .. 200, I want the total rows output to be set to 3500.
> Thanks, but as I said I do not want to use temporary tables, as this will
> absolutely swamp my tempdb due to the potential size of the query results.
> I considered your other suggestion previously, but then the question is
> how
> do I get the results of an EXEC into a variable? Keep in mind that I have
> to build the sql dynamically, and thus require sp_executesql. The
> following syntax doesn't work:
> SET @.totalRows = (EXEC sp_executesql @.mySqlStatement) ' doesn't parse
> If I can find the solution to how to set a variable based on a dynamic sql
> statement then I will be set.
> -mdb