Friday, February 24, 2012

"limit" in sql server

someone suggested I use:

select top 20 * from t
where pkc not in (select top 10 pkc from t order by pkc) order by pkc

to simulate the limit function in mysql.

i want to replace the 10 in the inner select with a variable. When I do this:

select top 20 * from t
where pkc not in (select top @.counter pkc from t order by pkc) order by pkc

it gives me in an error in sproc. Pls help!!It needs to be dynamic

DECLARE @.SQL varchar(8000), @.Counter int
SELECT @.Counter = 10
SELECT @.SQL = 'select top 20 * from t'
+ ' where pkc not in (select top '
+ CONVERT(varchar(15),@.counter)
+ ' pkc from t order by pkc) order by pkc'
SELECT @.SQL
EXEC(@.SQL)

No comments:

Post a Comment