select top query

brettokumar

hi i am created a store procedure like this for select top list user definlly create procedure ss(@s int) as select top @s sno from summa1 order by newid() it can not create and display error how to create

with regards, bretto

Colin Angus Mackay

In you are using SQL Server 2000 then this is not possible.

Recent blog posts: * Introduction to LINQ to XML (Part 1) - (Part 2) - (part 3) My website | Blog

Ashfield

I suspect you are in sql server 2000, which will not accept a parameter for the top clause. In 2005 it would work. You need to create and execute the sql dynamically for it to work in 2000.

Bob Ashfield Consultants Ltd

SomeGuyThatIsMe

in sql server 2000 try create procedure ss(@s int) as SET ROWCOUNT @s select sno from summa1 order by newid SET ROWCOUNT 0 ROWCOUNT should turn pink..i havnt used it in a while so i may have typed it wrong. I do not believe that Rowcount is available in sql server 2k5, it was replaced with something but i dont remember what. EDIT: ROWCOUNT is the correct one..@@ROWCOUNT wont work. you could also use dynamic sql like EXEC 'SELECT TOP ' + CAST(@s AS CHAR) + ' sno FROM summa1 ORDER BY newid'

Please remember to rate helpful or unhelpful answers, it lets us and people reading the forums know if our answers are any good.

modified on Friday, June 27, 2008 4:16 PM

SomeGuyThatIsMe

You can use SET @@rowcount or create procedure ss(@s int) as EXEC 'select top ' + CAST(@s AS CHAR) + ' sno from summa1 order by newid()'

Please remember to rate helpful or unhelpful answers, it lets us and people reading the forums know if our answers are any good.

Ashfield · modified on Friday, June 27, 2008 4:16 PM

I don't think this would work, I'm pretty sure @@rowcount is read only. I don't have sql server 2000 installed, but I have never seen @@rowcount being set, only ever read. What I think is meant is set rowcount, but this cannot be set to a parameter, so you are back to dynamic sql

SomeGuyThatIsMe wrote:

I do not believe that Rowcount is available in sql server 2k5,

Both rowcount (to set the number of rows returned) and @@rowcount (the number of rows affected) are both still alive and kicking in SQL Server 2005

Bob Ashfield Consultants Ltd

SomeGuyThatIsMe

It works fine i have it done in multiple places mostly in stored procedures, and in some other queries. SET ROWCOUNT it works well for randomizing the data you pull from a table with no PK. @@ROWCOUNT might be read only, ROWCOUNT is the correct one to use.

Please remember to rate helpful or unhelpful answers, it lets us and people reading the forums know if our answers are any good.

Ashfield

Sorry, you can set rowcount to a parameter, I just couldn't type when I tried it :-O

Bob Ashfield Consultants Ltd

Pete OHanlon

SomeGuyThatIsMe wrote:

You can use SET @@rowcount or

I'm pretty sure you're thinking of SET ROWCOUNT and not SET @@ROWCOUNT. @@ROWCOUNT tells you how many rows were affected by a statement.

Deja View - the feeling that you've seen this post before.

My blog | My articles

Alsvha

Although be aware that as far as I know, ROWCOUNT is being deprecated in 2K5 and it is removed from SQL Server 2K8 (or perhaps the one after 2K8)

--------------------------- Blogging about SQL, Technology and many other things

SomeGuyThatIsMe

yep thats what i read...they are replacing it with something, but i dont think it works quite the same.

Please remember to rate helpful or unhelpful answers, it lets us and people reading the forums know if our answers are any good.