Discrete Information

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.