SELECT TOP X ...

[bdreed35]

Does anyone know if I can do the following:

I am writing a Stored Procedure that needs to count the next "X" records in a temp table. I have tried the TOP keyword, and it works fine when it is a hard coded value. The problem is that the number of records that I am trying to count will change as we go, and it doesn't let me use the table field there.

Here is a sample:
SELECT COUNT(*)
FROM
(SELECT TOP 5 SiteNo, Treatment
FROM #EligibleAllocations
WHERE SiteNo = Site.SiteNo)

This is part of a larger statement and I need to change the 5 for a field from the larger statement. Any help pointing me in the right direction would be greatly appreciated. ~Brian

 

[sguslan]

declare @cnt int
declare @sqlstr

set @cnt=10

set @sqlstr='SELECT COUNT(*) FROM (SELECT TOP '
set @sqlstr=@sqlstr + cast(@cnt as varchar(10))
set @sqlstr=@sqlstr + ' SiteNo, Treatment
FROM #EligibleAllocations
WHERE SiteNo = Site.SiteNo)'

exec(@sqlstr)
________________________________________________________________________________
If you do not like change, get out of the IT business...

 

[tlbroadbent]

Declare @cnt
Set @cnt=5

--set the rowcount to limit the number of rows affected by the query
SET ROwCOUNT @cnt

SELECT SiteNo, Treatment
FROM #EligibleAllocations
WHERE SiteNo = Site.SiteNo

SET ROWCOUNT 0 If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm