Issue with SELECT TOP where TOP dynamic

Question

Post reply

Issue with SELECT TOP where TOP dynamic

tshad

SSCertifiable

Points: 5905
More actions
June 2, 2011 at 5:14 pm

#238526

I am trying to delete oldest records after the last n records where n could be 5 or 6 (this would be passed in).
I tried using the SET ROWCOUNT @Top to set the row count but that doesn't work since it would set it for the whole query where I only want it for the subquery in my query.
I am trying to do something like:
SELECT *
FROM OldPassword
WHERE UserID = @userid AND
OldPasswordId NOT IN (
SELECT TOP 4 OldPasswordId
FROM OldPassword
WHERE UserID = @userid
ORDER BY InsertDate)
This works fine.
But I need the Top 4 to be dynamic and I don't want to write dynamic SQL.
Can this be done?
Thanks,
Tom
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

mister.magoo SSC-Forever Points: 47174 More actions · Answer 1

Sure, SELECT TOP(@myVariable) etc. works just fine

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

Latheesh NK SSCertifiable Points: 6559 More actions · Answer 2

As magoo says, please declare a variable and assign the values. It just works for me.

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 3

But you HAVE to enclose the variable in parentheses

This works.

declare @x int = 10

select top (@x) *

from sys.all_objects

This doesn't. You'll get a syntax error.

declare @x int = 10

select top @x *-- look mom, no parentheses

from sys.all_objects

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

tshad SSCertifiable Points: 5905 More actions · Answer 4

That was my problem.

I forgot the parens.

Thanks,

Tom