Is there an equivalent of SQL_CALC_FOUND_ROWS in SQL Server?

Question

I have this mssql query:

with RESULT as(select  TITLE, URL, ROW_NUMBER() over (order by URL) as SeqValue from WEBSITE 
select * from RESULT where SeqValue>=20 and SeqValue<=40 

I'd like to know how many record this query would return if the where statement was not there. I try with select count(*) from RESULT and try with @@ROWCOUNT and many others way but did not work. I need TITLE and URL from select, and in the end i need total record for the select.

For example in mysql query i have prepareStatement using SQL_CALC_FOUND_ROWS:

select SQL_CALC_FOUND_ROWS TITLE, URL from WEBSITE limit ?, ?

and after this select i have:

select FOUND_ROWS()

In this example returned value is total record for the mysql query. Total record is same with LIMIT and without LIMIT directive. I convert database from mysql to mssql and i have problem with this. Please help me...

Answer 1

I had the same concern when trying to move from MySQL to SQL SERVER the solution is to inject this in your query:

COUNT (*) OVER () AS ROW_COUNT

ROWCOUNT appear in all rows in the result then it only remains for you to retrieve ROW_COUNT from the first row result (if exists) and not forget to reset the result pointer et Voila;-).

for example:

select COUNT (*) OVER () AS ROW_COUNT, URL from WEBSITE limit ?, ?

I hope it will help