Technical Article

Stored Procedure to SELECT NEXT x TOP y Records

,

This stored procedure implements a kind of

SELECT NEXT x TOP y Columns

FROM Table

WHERE Condition

ORDER BY Order.

It works on any table that has a unique id column and it works with any WHERE clause and any ORDER BY condition. The only drawback is its very poor performance on large tables. This is my first attempt to build a generic NEXT x TOP y solution so I would really welcome some comments and tips.

João Melo (joao.melo@inga.min-agricultura.pt)

--
--  sp_selectnextn by:
--      João Melo (joao.melo@inga.min-agricultura.pt)
--      2002-02-06
--
--  This stored procedure implements a kind of:
--
--      SELECT NEXT <x> TOP <y> <Columns> 
--      FROM <Table> 
--      WHERE <Condition> 
--      ORDER BY <Order>"
--
--   statement where: 
--       - <x> is @GroupNumber;
--       - <y> is @GroupSize;
--       - <Colimns> is @Columns;
--       - <Table> is @Table;
--       - <Condition> is @SqlWhere;
--       - <Order> is @SqlOrderBy.
--

CREATE PROCEDURE sp_selectnextn
@TableName VARCHAR(64),
@Columns VARCHAR(1000),
@IdentityColumn VARCHAR(64),
@GroupNumber INT,
@GroupSize INT,
@SqlWhere VARCHAR(1000),
@SqlOrderBy VARCHAR(1000)

AS

DECLARE @SqlString NVARCHAR(1000)
DECLARE @SqlString2 NVARCHAR(1000)
DECLARE @PreviousRecords INT

SET @PreviousRecords = (@GroupSize * @GroupNumber) - @GroupSize

SET @SqlString2 = N'(SELECT TOP ' + 
CAST(@PreviousRecords AS NVARCHAR(32)) + 
N' ' + @IdentityColumn + 
N' FROM ' + 
@TableName

IF @SqlWhere + '' <> ''
BEGIN
SET @SqlString2 = @SqlString2 + 
N' WHERE ' + 
@SqlWhere
END

IF @SqlOrderBy + '' <> ''
BEGIN
SET @SqlString2 = @SqlString2 + 
N' ORDER BY ' + 
@SqlOrderBy
END

SET @SqlString2 = @SqlString2 + N')'

SET @SqlString = N'SELECT TOP ' + 
CAST(@GroupSize AS NVARCHAR(32)) + 
N' ' + 
@Columns + 
N' FROM ' + 
@TableName + 
N' WHERE (' + 
@IdentityColumn + 
N' NOT IN ' + 
@SqlString2 + 
N')'

IF @SqlWhere + '' <> ''
BEGIN
SET @SqlString = @SqlString + 
N' AND ' + 
@SqlWhere
END

IF @SqlOrderBy + '' <> ''
BEGIN
SET @SqlString = @SqlString + 
N' ORDER BY ' + 
@SqlOrderBy
END

EXEC sp_executesql @SqlString
GO

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating