I’m trying to figure out how to make the number of returned rows dynamic in my SQL Server query. I want to use a variable to control how many records come back from my SELECT statement.
Here’s what I’m attempting to do, but it doesn’t work:
DECLARE @rowLimit int
SET @rowLimit = 15
SELECT TOP @rowLimit * FROM MyTable
This gives me an error in SQL Server. Is there a proper way to make the row count dynamic using a variable? I need this to work with SQL Server 2008 and later versions.
hmm, that’s interesting - what error message are you seeing? and which version of SQL Server Management Studio are you runnig? sometimes older versions get weird about variable syntax even when the server handles it fine.
That syntax should work fine in SQL Server 2008+. However, I’ve encountered similar issues before; explicitly casting the variable often resolves these errors. Consider using SELECT TOP (CAST(@rowLimit AS INT)) * FROM MyTable to see if that works. Additionally, ensure that the variable isn’t null, as this can cause unexpected behavior with the TOP clause. My experience has shown that using parentheses around the variable and adding the cast resolves most dynamic TOP issues in various SQL Server environments.
your code looks good for SQL Server 2008+. maybe check for typos? I usually use SELECT TOP (@rowLimit) * FROM MyTable with parentheses around variables - it works perfectly for me.