Select TOP @RowCount?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I would like to be able to parameterize the TOP rows value in a stored procedure, like
@RowCount int

SELECT TOP @RowCount * FROM tabl

Is there a syntax that makes this possible, or must it always be hardcoded

Thanks
Dean Slinde
 
AFAIK (and by no means am I saying there isn't another way) you can
dynamically create a procedure so that you specify the top in the
definition:

create usp_SomeProcedure
@Top int
as

declare @SQL varchar(1000)

set @SQL='
s elect top ' + (str(@NumRecords)) + ' from myTable'

execute (@SQL)
execute usp_SomeProcedure
execute ('drop procedure usp_SomeProcedure')


Here's a similar method using SQL that might work...
http://www.net-language.com/CodeExample.aspx?i=440

HTH,

Bill
 
Hi,

Either use William's suggestion or create dynamically that part of sql
statament.
 
set rowcount @RowCount
select * from table
set rowcount = 0 -- IMPORTANT!

No dynamic SQL required.
 
I don't believe you can do it the clean way that you're asking. This
will work though:

------------------------------------------
Create procedure dbo.Eraseme

@RowCount varchar(5)

as

exec('Select top ' + @RowCount + ' * From MyTable')
------------------------------------------

Then you call it as: dbo.eraseme 2


Guy Bloomfield
 
Back
Top