How to select top rows from a table without hard coding the top value
Build intelligent, mission-critical applications using a scalable, hybrid database platform that has everything built in—from in-memory performance and advanced security to in-database analytics.
Most of the time developers hard code the TOP value when doing a SELECT from a table. They are looking for first few of row as a way to probe data in a table. A lot of times there are valid cases that arrive when you want the top value passed as a parameter instead. For example: Created a simple table table_name as,
1 create 2 table table_name( 3 column_00 int, 4 column_01 varchar(30), 5 column_02 varchar(40), 6 column_03 varchar(12), 7 column_04 varchar(30) 8 );
Once the table is populate with data, you can create sql statements or use this code in stored procedures to retrieve data by supplying the ‘top’ value as a parameter to your queries.
1 declare @top_rows int 2 set @top_rows = 42 3 4 select 5 top(@top_rows) 6 A.column_00, 7 A.column_01 8 from table_name A 9 where A.column_02 = ‘search_string’