How would one go about having a sproc that allows a consumer to optionally specify the number of rows returned?
If no row count is specified, then all rows returned.
azure-sql-databasesql serverstored-procedurestop
How would one go about having a sproc that allows a consumer to optionally specify the number of rows returned?
If no row count is specified, then all rows returned.
Best Answer
The "easiest" way is to parameterize input to TOP. The number I'm using is the maximum value for BIGINT. You'll probably never have more rows than this returned by a query.
Just be careful, because you can introduce parameter sniffing symptoms here.
If you review the plans for both of these, the TOP operator for both is expecting one row.
Whichever input to top executes first will be cached, unless you recompile, or add an OPTIMIZE FOR hint.
You should also be aware that adding a TOP operator may introduce row goals.