Getting Started with Adobe After Effects - Part 6: Motion Blur
A collection of quick technology learning tips from what people around you learn every day

SQL Server - Select top count dynamically

Sep 21 2011 3:33AM by RKA   

While trying to retrieve the select top count dynamically, initially tried this way

<pre class="brush: plain">
Declare @noOfRowsPerChunk int
set @noOfRowsPerChunk = 5
select top @noOfRowsPerChunk RecordID from myTable
</pre>

which throwed the syntax error.

Fix: Add parentheses to the top variable

<pre class="brush: plain">
select top (@noOfRowsPerChunk) RecordID from myTable
</pre>
Read More..   [10 clicks]

Published under: SQL Server Tips ·  ·  ·  · 


RKA
92 · 2% · 598
18 Readers Learned this
deepakdangwal Learned this on 9/21/2011 3:51:00 AM
Profile
pilipala Learned this on 9/22/2011 12:44:00 AM
Profile
oeikli Learned this on 9/22/2011 2:03:00 AM
Profile
DCS Learned this on 9/22/2011 2:37:00 AM
Profile
rhodrie Learned this on 9/22/2011 3:07:00 AM
Profile
oorabi Learned this on 9/22/2011 3:08:00 AM
Profile
Sultan Learned this on 11/15/2011 3:27:00 AM
Profile
RKA Learned this on 9/21/2011 2:18:00 AM
Profile
Guru Samy Learned this on 3/29/2012 3:42:00 AM
Profile · Blog
satyajit Learned this on 3/29/2012 4:20:00 AM
Profile · Blog
Naresh Yadav Learned this on 2/9/2015 11:40:00 AM
Profile
Flashspot Learned this on 2/25/2015 3:41:00 PM
Profile
mshijat Learned this on 9/22/2011 9:04:00 AM
Profile
Mahadevan N Learned this on 9/22/2011 1:25:00 PM
Profile · Twitter
woodgatp Learned this on 9/23/2011 12:13:00 AM
Profile
Pothiraj Learned this on 9/26/2011 5:45:00 AM
Profile
G.S.Narayana Learned this on 10/5/2011 1:25:00 AM
Profile
Neeraj Kaushik Learned this on 10/24/2011 12:55:00 AM
Profile · Facebook · Twitter
23
 
7
 
18
Learned
 
0
Incorrect
 
0
Interesting
 
0
Forgotten



Submit

8  Comments  

  • I never understood reason behind restricting users to use paranthesis in this context. Ideally, it should work without paranthesis also. Since based on "@" it can identify it as an variable can substitute its value...

    commented on Sep 21 2011 3:37AM
    Ramireddy
    3 · 40% · 12972
  • I think the parenthesis is needed because it also can take an expression. For example you can run a query like the following:

    SELECT TOP (SELECT MaxRows FROM SetupTable) *
    FROM sys.tables
    
    commented on Sep 21 2011 4:26AM
    Jacob Sebastian
    1 · 100% · 32235
  • Exactly what Jacob said. I already thought of blogging about this. It will be posted soon :)

    commented on Sep 21 2011 6:30AM
    Madhivanan
    2 · 40% · 13039
  • I guess it also prevents mistakes.

    It could be easier to make mistake if you could write: SELECT TOP 1 11, B FROM tData SELECT TOP 11 1, B FROM tData SELECT TOP 1 [PERCENT], 1 AS LineNb FROM tPercentage

    commented on Sep 22 2011 2:43AM
    Sergejack
    41 · 4% · 1395
  • Of course, this does not work in SQL Server 2000.

     declare @n int
     set @n = 3
    

    -- works in 2K8 - Fails in 2K

     select top (@n) * from tally
    

    -- works in 2K8 and 2K

     set Rowcount @n
     select * from tally
     set Rowcount 0
    
     select * from tally
    
    commented on Sep 22 2011 7:39AM
    Bradley M. Small
    349 · 0% · 121
  • Thanks Bradley for the information..

    commented on Sep 22 2011 10:39PM
    RKA
    92 · 2% · 598
  • You're welcome. I have to keep SQL2K as well as SQL2K8 db's going and continue development in both environments. Lots of times, great tips come along, that just flat out don't help much in SQL2K ... At least I don't have to keep SQL7 and 6.5 working anymore :)

    commented on Sep 23 2011 10:14AM
    Bradley M. Small
    349 · 0% · 121

Your Comment


Sign Up or Login to post a comment.

"SQL Server - Select top count dynamically" rated 5 out of 5 by 23 readers
SQL Server - Select top count dynamically , 5.0 out of 5 based on 23 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]