Select number of records from table

Hi,

Here is a stored procedure that will do it for you, just compile it on your server and run it using either 1 or 0 as a parameter.

You will see if you use 1 as a parameter it will return top 5, otherwise 0 will return the next 5

<br />
execute SP_GetTop5CP 1<br />
execute SP_GetTop5CP 0<br />
execute SP_GetTop5CP 1<br />

if (object_id('SP_GetTop5CP') is not null)
begin
    drop proc SP_GetTop5CP
end
go
/*
This stored procedure will return the top 5 if called with 1 parameter
and will return the 6-10 if called with 0 parameter
*/
create proc SP_GetTop5CP @First5 bit = 1
as
begin
    declare @i int
    select top(10) itemCode,ItemName,Price into #1 from Item
    alter table #1 add IDX int
    set @i = 0
    update #1 set IDX = @i, @i = @i + 1
    if (@First5 = 1)
    begin
        --you want to get the first 5
        delete from #1 where IDX > 5
    end
    else
    begin
        --you want to get the next 5
        delete from #1 where IDX <= 5
    end
    alter table #1 drop column IDX
    select * from #1
end
go
grant exec on SP_GetTop5CP to public
go

Hope this helps

Regards

Terence