Convert MySQL query to Microsoft SQL Server

Question

I need to convert this MySQL query to a SQL Server query where the syntax 'LIMIT ?, ?' is my main main problem because isn't compatible with SQL Server.

At the same time I don't know the meaning of the clause at the end SELECT FOUND_ROW().

I am beginner with databases so I would appreciate any assistance.

SELECT SQL_CALC_FOUND_ROWS TITLE, URL FROM SITE WHERE CITY='Berlin' LIMIT ?, ?

...

stmt.setInt(1, offset);
stmt.setInt(2, numRecords);

...

rs = stmt.executeQuery("SELECT FOUND_ROWS()");
if (rs.next())
this.noOfRecords = rs.getInt(1);

...

FOUND_ROWS() is not replicated reliably, and should not be used with databases that are to be replicated. — zod
– zod, Commented Apr 12, 2013 at 20:18

Ed Gibbs · Accepted Answer · 2013-04-12 20:57:04Z

1

If the MySQL is:

SELECT URL
  FROM SITE
  WHERE CITY='Berlin'
  LIMIT 30, 20

Then it's returning rows 31 through 50. In SQL Server you can do something like this:

WITH somerows AS (
  SELECT TOP 50 URL, ROW_NUMBER() OVER (ORDER BY URL) AS SeqValue
  FROM SITE
  WHERE CITY='Berlin'
)
SELECT * FROM somerows
WHERE SeqValue BETWEEN 31 and 50

Note that the 50 in the SQL Server query is the sum of the MySQL LIMIT values, and that the 30 in MySQL is zero-indexed so it becomes 31 in SQL Server, where the ROW_NUMBER counter is one-indexed. Many thanks to Kevin Suchlicki for pointing this out in the comments below.

Also note that, unlike your MySQL query, you have to order the rows with the SQL Server query.

Finally, SQL_CALC_FOUND_ROWS is a MySQL keyword that enables the FOUND_ROWS() function. It's explained in the documentation. There's no SQL Server equivalent. You'll need to count the rows some other way to set this.noOfRecords in your code, but you should be able to work around that.

edited Apr 12, 2013 at 20:57

answered Apr 12, 2013 at 20:25

Ed Gibbs

26.4k4 gold badges50 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Kevin Suchlicki Over a year ago

Ed Gibbs' answer is mostly right. Slight corrections: the second parameter in the MySQL LIMIT clause is the count of records to display, and the first parameter is zero-based. So the equivalent LIMIT parameters to Ed's TSQL syntax are LIMIT 40, 10

Ed Gibbs Over a year ago

Thanks for the clarification @KevinSuchlicki, I muffed that one! I'll update my answer.

Sergio A. · Accepted Answer · 2013-04-12 20:26:47Z

0

The question marks are used in MySQL to bind parameters to a statement. So based on your code, the parameters being passed are the offset and number or records to be used by the LIMIT function. Seems to me that it's paging the results. Try this SQL on your SQL Server statement as replacement of the MySQL

DECLARE @offset INT
DECLARE @numRecords INT
SET @offset = 1
SET @numRecords = 2

SET ROWCOUNT @numRecords
WITH RowsResult AS 
(
    SELECT ROW_NUMBER() OVER (ORDER BY TITLE) AS RowNum,
        TITLE, 
        URL 
    FROM [SITE] 
    WHERE CITY='Berlin' 
    ORDER BY TITLE
)
SELECT  TITLE
FROM    URL
WHERE   RowNum > @offset
SET ROWCOUNT 0

answered Apr 12, 2013 at 20:26

Sergio A.

4002 silver badges9 bronze badges

Collectives™ on Stack Overflow

Convert MySQL query to Microsoft SQL Server

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related