Friday, February 24, 2012

How do I dynamically change the "TOP X" portion of a SELECT

I'm sure I'm missing something. I am returning the TOP X number of customers by revenue and I'd like to change the number of records returned by passing a parameter but I keep getting an error.

@.TopX int ( or varchar)

SELECT @.TopX CompanyName, Amount FROM Sales Where....

Why will this not work?

Only works in SQL Server 2005 or SQL Express:

SELECT TOP (@.Topx) ...

|||

Only works in SQL Server 2005 or SQL Express:

SELECT TOP (@.Topx) ...

|||
DECLARE @.stmtvarchar(500)DECLARE @.top varchar(5)SET @.top ='10'SET @.stmt ='SELECT TOP ' + @.top +' * FROM [Products]'EXEC(@.stmt)
You can dynamically create a sql statement:|||

Upgraded to 2005 and that worked perfectly...

Thanks!

No comments:

Post a Comment