Sometimes we do very ugly thing just because of missing simple tricks.
 
I believe all of the devs who work with sql use SELECT TOP command as they need. This is a very simple sql command.
 

SELECT TOP 10 * FROM YOURTABLE

 
Now, sometimes you need to use variable instead of constant number (say 10). If you write the query as
 

SET @count = 20
SELECT TOP @count * FROM YOURTABLE

you will get a syntax error.

 
To solve this issue, we sometimes write dynamic sql (Sql statements constructed inside a string variable) and execute that.
 
But we all know dynamic sql is always bad for many reasons and we should avoid that as far as we can.
 
We can avoid dynamic sql in this scenario very easily with a simple trick. Once you know that, you will laugh at yourself if you really used dynamic sql for this scenario.
 
The Solution:
 

SET @count = 20
SELECT TOP (@count) * FROM YOURTABLE

 
Did you mark the trick? Yes you just need enclose the variable with a Bracket ().   

Comments

ron's picture

I assume your example is for MSSQL only? MySQL/Oracle does not support TOP(n) function. 

ron's picture

 

MySQL equivalent for this is 
 
SELECT * FROM YOURTABLE LIMIT 0,10;
 
And using variables the sql will be
 
SET @count=10;
PREPARE statement FROM 'SELECT * FROM YOURTABLE LIMIT 0, ?';
EXECUTE statement USING @count;
 
MySQL supports DECLARE statement but only inside BEGIN .. END, so DECLARE is useful only for stored procedures or custom functions.
mahmud's picture

Thanks Ron bhai for your input. I really posted the tips for MS sql. I should have mentioned this. I have changed the title to indicate this.

Great to see how this can be done using mysql.

 

Add new comment