select top @variable 1

[wooody1245]

Is there a way that I can use a variable in a select top statement like the one below?
select top @max_results from my_table where column1 = @variable

I'm passing @max_results to a stored procedure to limit the number of rows returned.

 

[bboffin]

You can build up the SELECT statement in a varchar variable and then EXEC it:

CREATE PROCEDURE GetMaxResults
@Variable int,
@Max_results int
AS
DECLARE @SQL varchar(500)
set @sql = 'select top ' + CAST(@Max_results as varchar(10) + ' * FROM WHERE column1 = ' + cast(@Variable as varchar(10))
EXEC (@SQL)

Bob Boffin

 

[mrdenny]

You can also set the ROWCOUNT setting using the variable. Setting the ROWCOUNT setting has the same basic effect as using TOP without the overhead of using synamic SQL.

Create Procedure p1
   @max_results int
as
set ROWCOUNT @max_results
select *
from table
go

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[rmnastor]

the code posted by BBOFIN is better and simpler... applause!

 

[jbenson001]

I belive Denny's code is much better and more readable and maintainable...

Jim

 

[hmckillop]

My 2 cents

There are arguments for both, I normally avoid using dynamic SQL for issues which have been debated here previously, though MrDenny's option can have problems if you dont remember to reset the rowcount, or if in the SELECT statement you have subselect statements or derived queries in functions, which the rowcount is then applied to them.

In answer to what you are doing, based on the actual question I would use MrDenny's answer!

"I'm living so far beyond my income that we may almost be said to be living apart

 

[SQLDenis]

Fortunately for us SQL Server 2005 allows a variable for the top statement but until then I would use mrdenny's code with the addition of setting rowcount to 0 at the bottom



“I sense many useless updates in you... Useless updates lead to defragmentation... Defragmentation leads to downtime...Downtime leads to suffering..Defragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" --

http://sqlservercode.blogspot.com/

 

[wooody1245]

I used bboffin's solution, but if I had it to do again I would use mrdenny's solution. It took me a while to get the string formatted properly. Thanks for the suggestions!