Is there a value that I can use in a SELECT TOP that will return all rows?

I'm allowing the end user to define how many rows are returned by a query (SELECT TOP (@x)). Is there a value that can be entered where all rows are returned? Or do I have to dynamically create the query without the TOP (@x) if they want all rows returned?

I'm using SQL Server 2012.

Well, it looks like TOP is a BIGINT if you aren't using a PERCENT. That means you could pass in the max value of BIGINT,

SELECT TOP (9223372036854775807) * FROM table1

I seriously doubt you will ever see a table that large. I'm not sure what kind of effect that would have on the query plan though.

StackExchange Database Administrators Q#147112, answer score: 19