Geek Answers Handbook

Is there an SQL_CALC_FOUND_ROWS equivalent in SQL Server?

I have this mssql request:

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 would like to know how many records will be returned in this request if there were not there. I try with select count(*) from RESULT and try with @@ROWCOUNT and many other ways, but it does not work. I need a TITLE and a URL from select, and in the end I need a complete entry to select.

For example, in a 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, the return value is a common entry for the mysql query. The general record is the same as LIMIT without the LIMIT directive. I am converting a database from mysql to mssql and I have a problem with this. Please help me...

+4
source share
3 answers

I had the same problem when you tried to switch from MySQL to SQL SERVER the solution should enter this in your query:

 COUNT (*) OVER () AS ROW_COUNT

ROWCOUNT appears in all rows of the result, then it remains only to get ROW_COUNT from the result of the first row (if exists) and do not forget to reset the result pointer and Voila; -).

eg:

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

I hope this helps

+7
source

"RESULT" will include every entry in WEBSITE, right? So just "select count (*) from WEBSITE".

I would expect

  with result as(...what you have...) select count(*) from result;

To work too, with some unnecessary work. What doesn't it do, what do you want?

0
source

to try:

 declare @row_count int select @row_count = count(*), URL from WEBSITE limit ?, ? select @row_count
0
source

More articles:


All Articles