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
23
 
7
 
18
 
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]