Sometimes, we do very ugly things 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:
DECLARE @count int
SET @count = 20
SELECT TOP @count * FROM YOURTABLE
This will give you 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:
DECLARE @count int
SET @count = 20
SELECT TOP (@count) * FROM YOURTABLE
Did you mark the trick? Yes, you just need enclose the variable with a Bracket ().
By the way, this trick is Microsoft SQL Server specific. Experts from Oracle or mysql may post alternatives from those platforms.
