CODEWITHSUNDEEP
sqlsql-serverstored-procedures
Dr. Rajesh Rolen
Dr. Rajesh Rolen

Reputation: 14285

Take top count dynamically

I want to create a stored procedure which takes integer values as a @top from me so that I can use it in my query, but it's not allowing me to set value of top dynamically.

select top @top * from (select url,
count(1) as shared from tblshared
group by url, uniqid having
uniqid = @uniqid) as sha  order by
shared desc

I want to retrieve top n records from table so I want to pass the value of n in stored procedure and it will return me that number of top records.

Note: I don't want to use exec.

Thanks.

Upvotes: 1

Views: 433

Answers (4)

Jahan Zinedine
Jahan Zinedine

Reputation: 14874

You can use ROW_NUMBER() isntead of top

with t1 as(
    select url,row_number() over(
                partition by url, uniqid order by url, uniqid desc) as shared 
    from tblshared
    where uniqid = @uniqid )

select * from t1 where shared < @top

REF

Upvotes: 2

martin clayton
martin clayton

Reputation: 78155

You could use SET ROWCOUNT:

SET ROWCOUNT @top
SELECT ...
SET ROWCOUNT 0

Upvotes: 1

Robb
Robb

Reputation: 3851

That will work fine if you wrap @top in brackets

select top (@top) * 
from ( 
    select url, count(1) as shared 
    from tblshared 
    group by url, uniqid 
    having uniqid=@uniqid) as sha 
order by shared desc

Upvotes: 4

user545619
user545619

Reputation: 

declare @v1 int
set @v1 = 25
set rowcount @v1
select * from MyTable Order by DateColumn
set rowcount 0

Upvotes: 0

Related Questions