I was writing a query for Reporting Services (SSRS) and I really wanted to use a parameter to allow the user to choose how many of the "top" results should be retrieved/shown. With SQL Server 2005, the TOP expression is now parameterized however attempting to use it in an SSRS dataset like this:
SELECT TOP ( @topN ) p.person_id, p.first_name,p.last_name
FROM core_person p WITH(NOLOCK)
ORDER BY p.last_name
only gave me this error:
Rats! It should have worked. My @topN parameter is properly defined as a Report Parameter of type Integer with a default value of 20.
Looking around I found a lot of complicated ways around the issue some of them involving creating dynamic statements and such like this one (link). Way too complicated for me and I hate dynamic SQL -- I just say NO.
It turns out with a little creative oob thinking you can just trick SSRS by declaring another variable in your dataset and then setting it to the value of your Report Parameter like this:
DECLARE @NewTop int
SET @NewTop = @topN
SELECT TOP ( @NewTop ) p.person_id, p.first_name, p.last_name
FROM core_person p WITH(NOLOCK)
ORDER BY p.last_name
Problem solved.