Passing the parameter to TOP keyword

Hi All,

I want create a stored procedure by passing the parameter to top nth record

for Ex

create proc test1
(
@x integer
)
as
begin

select top @x empid from emp order by empid desc

end
when iam executing iam getting error .Please let me know any other alternative way

sumanreddymca
Profile Answers by sumanreddymca Questions by sumanreddymca
Jan 31st, 2008
2
2492

Questions by sumanreddymca

PL/SQL

Answer

Showing Answers 1 - 2 of 2 Answers

xambrose
Profile Answers by xambrose

Jul 19th, 2008

create proc test1
(
@x integer
)
as
begin
SELECT empid FROM
(SELECT empid FROM emp ORDER BY empid )
WHERE ROWNUM = @x;
end

shruti.potdar
Profile Answers by shruti.potdar

Dec 10th, 2008

Solution to your problem:

Create Procedure TEST1( p_num number)
is
v_eid number;
begin
select empid into v_eid
from EMP
where empid=(select A.empid
from (SELECT empid, rank() over (order by empid desc) RNK
FROM emp) A
where A.RNK=p_num);

dbms_output.put_line('Top'|| p_num || 'emp is ::'|| v_eid);

End;
/