MS SQL has the TOP clause, and it works very similar to MySQL’s LIMIT clause. However, the TOP clause in MS SQL provides no sort of OFFSET equivalent. Thus the TOP clause is virtually useless for a proper paging control. Let’s see one way we can work around that limitation:
For reference, here are the CREATE and INSERT statements for the [users] table used in this example.
CREATE TABLE [users] ( user_id INT PRIMARY KEY, username VARCHAR(16), password VARCHAR(16), email VARCHAR(128) ); INSERT INTO [users] (user_id, username, password, email) SELECT 1, 'bob', 'bob!password', 'bob@gmail.com' UNION ALL SELECT 2, 'john', 'john!password', 'john@yahoo.com' UNION ALL SELECT 3, 'kim', 'kim!password', 'kim@msn.com' UNION ALL SELECT 4, 'rachel', 'rachel!password', 'rachel@aol.com' UNION ALL SELECT 5, 'frank', 'frank!password', 'frank@email.com';
Now, let’s create a stored procedure to search our [users] table. It takes three paramaters. The @email paramater is a full or partial e-mail to search. @page is the page to be displayed, and @per_page is the number of results per page.
CREATE PROC searchUsersByEmail
@email VARCHAR(128),
@page SMALLINT,
@per_page SMALLINT
AS
SET @last_page = (@nbr_results - 1) / @per_page + 1;
SELECT
@nbr_results = COUNT(*)
FROM [users] AS u
WHERE u.email LIKE '%' + @email + '%';
SET @last_page = @nbr_results / @per_page;
IF (@page = 1)
BEGIN
-- Special first page case:
SELECT TOP (@per_page)
u.user_id,
u.username,
u.email
FROM [users] AS u
WHERE u.email LIKE '%' + @email + '%'
ORDER BY u.email ASC;
END;
ELSE IF (@page = @last_page)
BEGIN
-- Special last page case:
SELECT s0.*
FROM (
SELECT TOP (@nbr_results - (@last_page - 1) * @per_page)
u.user_id,
u.username,
u.email
FROM [users] AS u
WHERE u.email LIKE '%' + @email + '%'
ORDER BY u.email DESC
) AS s0
ORDER BY s0.email ASC;
END;
ELSE IF (@page > @last_page)
BEGIN
-- Error / Empty set case, replace with error if desired.
SELECT
u.user_id,
u.username,
u.email
FROM [users] AS u
WHERE 1 = 0;
END;
ELSE
BEGIN
-- All other cases:
SELECT s1.*
FROM (
SELECT TOP (@per_page) s0.*
FROM (
SELECT TOP (@page * @per_page)
u.user_id,
u.username,
u.email
FROM [users] AS u
WHERE u.email LIKE '%' + @email + '%'
ORDER BY u.email ASC
) AS s0
ORDER BY s0.email DESC
) AS s1
ORDER BY s1.email ASC;
END;
Now let’s test our search procedure by searching for all e-mails with “.com”:
[searchUsersByEmail] @email = '.com', @page = 1, @per_page = 3; user_id username email ----------- -------- ---------------- 1 bob bob@gmail.com 5 frank frank@email.com 2 john john@yahoo.com [searchUsersByEmail] @email = '.com', @page = 2, @per_page = 3; user_id username email ----------- -------- ---------------- 3 kim kim@msn.com 4 rachel rachel@aol.com
That’s it. Now you have a functional procedure in MS SQL as an alternative to MySQL’s LIMIT x OFFSET y clause.
Tags: MS SQL, Pagination, SQL