Dynamic Top N Query

�a

Dynamic Top N Query - 10.01.2008

We use TOP N sql command to show top results of a sql query. However we can't use N as dynamic or parametric in TOP N command like getting value of N from a parameter table or from a parameter of a stored procedure. We can use SET ROWCOUNT command for this purpose. Syntax of this command is SET ROWCOUNT N where n is the count of rows to return where 0 means all rows will be returned. You have to set set rowcount to 0 after executing a command in order not to following queries return top n records.

DECLARE @rowCount int
SET @rowCount = (SELECT ParameterValue FROM Parameters WHERE ParameterId=12)
SET ROWCOUNT @rowCount
SELECT * FROM MyTable ORDER BY MyColumn
SET ROWCOUNT 0

CREATE PROC MyQuery
@rowCount int
AS
SET ROWCOUNT @rowCount
SELECT * FROM MyTable ORDER BY MyColumn
SET ROWCOUNT 0
GO

Clues,Codes

Add Comment

First Name Last Name
Web Site
E-Mail
Comment
Security Picture