Re: SELECT TOP

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: David Gugick (davidg-nospam_at_imceda.com)
Date: 01/23/05 

Date: Sun, 23 Jan 2005 13:16:29 -0500

Elie Grouchko wrote:
> Hi All
>
> I am writing a stored procedure to retrieve the top n rows of a SELECT
> statement.
>
> I tried passing the number of rows as a variable (topcount in the
> example), but I get a syntax error:
>
> Am I missing something?
>
> Thanks in advance
>
> Elie Grouchko
>
> CREATE PROCEDURE Proc_SelectTopForumCommentsOrderByDate
> @topcount int,
> @twc_comment_subject int
> AS
> SELECT TOP @topcount * FROM [twt_comment] WHERE
> ([twc_comment_subject] = @twc_comment_subject)
> AND ([twc_comment_id] != @twc_comment_subject)
> AND ([twc_comment_state] = 2) ORDER BY [twc_comment_date] DESC

SET ROWCOUNT, as Jacco mentioned is one workaround since the TOP
statement does not allow variables in its current implementation. The
other option is to use dynamic SQL. SET ROWCOUNT does not work in all
cases.

For example, the following query does not respect the ROWCOUNT setting
on inner SELECT.

SET ROWCOUNT 100
Select DISTINCT * From (
  SELECT a, b, c
  From TableA
  Where x = 3 )

You'll get only 100 rows in the final select, but the sample is on all
rows. To implement this you'll need to use TOP.

Select DISTINCT * From (
  Select TOP 100 a, b, c
  From TableA
  Where x = 3)

Your example, however, should be fine.

Despite the fact that scope of the ROWCOUNT is only for the SP in
question, it's a good idea to set it back to 0 immediately after the
SELECT. 

-- 
David Gugick
Imceda Software
www.imceda.com

Relevant Pages

  • Have I written an inefficient stored procedure?
    ... I am wondering if my stored procedure has been badly written? ... SET ROWCOUNT @nNumRecsToFind ... SET ROWCOUNT @nJustReturnTheseRecs ... I didn't use dynamic SQL (with a @variable for use with the TOP ...
    (microsoft.public.sqlserver.programming)
  • Re: TOP statement doesnt accept a variable
    ... DECLARE @rowcount INT ... SET ROWCOUNT @rowcount ... > The following stored procedure is designed to return the ...
    (microsoft.public.sqlserver.programming)
  • RE: Stored Procedure: Select TOP @param
    ... SELECT COUNTFROM sysobjects ... DECLARE @rc INT ... SET ROWCOUNT @rc ... > I want to call a stored procedure, but only return a limited number of rows. ...
    (microsoft.public.sqlserver.programming)
  • Re: How can I assign the result of dateadd to a variable ?
    ... The SET ROWCOUNT setting limits the number of rows ... affected by the query to the given number. ... queries from the same connection, unless set in a stored procedure (then, ...
    (microsoft.public.sqlserver.programming)