Man-wai Chang wrote:
>

"Man-wai Chang" <info@softmedia.hk> wrote in message
news:uBDwLnItGHA.1304@TK2MSFTNGP06.phx.gbl...
> ALTER procedure [dbo].[fetch_customer]
> @pageno integer,
> @pagelength integer,
> @sorting varchar(32),
> @keyword varchar(100)
> as
> set nocount on
>
> declare @startrow integer
>
> if @pageno<0 begin
> declare @ttl integer
> select @ttl=count(*) from customer
> set @startrow=@ttl/@pagelength*@pagelength+1
> end
> else begin
> set @startrow=(@pageno-1)*10+1
> end
>
> create table #result (
> unicode char(20),
> code char(20),
> name char(60),
> rcnb integer
> )
>
> if @sorting=' ' begin
> if @keyword=' ' begin
> declare mycust scroll cursor
> for select unicode, code, name from customer
> order by unicode
> end else begin
> declare mycust scroll cursor
> for select unicode, code, name from customer
> where unicode like '%'+@keyword+'%'
> order by unicode
> end
> end else if @sorting='name' begin
> if @keyword=' ' begin
> declare mycust scroll cursor
> for select unicode, code, name from customer
> order by name
> end else begin
> declare mycust scroll cursor
> for select unicode, code, name from customer
> where unicode like '%'+@keyword+'%'
> order by name
> end
> end else if @sorting='code' begin
> if @keyword=' ' begin
> declare mycust scroll cursor
> for select unicode, code, name from customer
> order by code
> end else begin
> declare mycust scroll cursor
> for select unicode, code, name from customer
> where unicode like '%'+@keyword+'%'
> order by code
> end
> end
>
> declare @unicode char(20), @code char(20), @name char(60)
> declare @ii integer
>
> set @ii=0
> open mycust
> fetch absolute @startrow from mycust into @unicode, @code, @name
> while ((@@fetch_status=0) and (@ii < @pagelength)) begin
> insert into #result values ( @unicode, @code, @name, @startrow+@ii )
> set @ii = @ii+1
> fetch next from mycust into @unicode, @code, @name
> end
>
> close mycust
> deallocate mycust
> set nocount off
> select * from #result
> drop table #result
>
>
> --
> SoftMedia Technology Co., Ltd.
> Website: http://www.softmedia.hk Tel: (852)2743 4228
> * TryEasy Accounting/POS/Trading/ERP solutions

Man-wai Chang wrote:
> declare mycust scroll cursor
> for select unicode, code, name from customer
> where unicode like '%'+@keyword+'%'
> order by name

> declare @unicode char(20), @code char(20), @name char(60)
> declare @ii integer
>
> set @ii=0
> open mycust
> fetch absolute @startrow from mycust into @unicode, @code, @name
> while ((@@fetch_status=0) and (@ii < @pagelength)) begin
> insert into #result values ( @unicode, @code, @name, @startrow+@ii )
> set @ii = @ii+1
> fetch next from mycust into @unicode, @code, @name
> end

"Man-wai Chang" <toylet.toylet@gmail.com> wrote in message
news:uLqh$GJtGHA.1512@TK2MSFTNGP03.phx.gbl...
> Uri Dimant wrote:
>> Hi
>> What is the final result? Can you post DDL+ sample data+ expected result?
>> I'm sure it could be done without using a cursor
>>
>
> It's for browsing the table page by page, used by a Foxpro form.
>
> --
> .~. Might, Courage, Vision, SINCERITY. http://www.linux-sxs.org
> / v \ Simplicity is Beauty! May the Force and Farce be with you!
> /( _ )\ (Ubuntu 6.06) Linux 2.6.17.6
> ^ ^ 19:08:01 up 15 days 2:31 0 users load average: 1.00 1.04 1.01
> news://news.3home.net news://news.hkpcug.org news://news.newsgroup.com.hk

> declare @maxrow int
> set @maxrow = @pageLength*(@pageno+1)
>
> declare @tab table (_ID_ int not null identity(1,1),unicode
> char(20),@code char(20),name char(20))
>
> set rowcount @maxrow
> insert into @tab(unicode,code,name)
> select unicode,code,name from customer where ....
> order by name
> set rowcount 0
>
> select unicode,code,name from @tab where _ID_>= @StartRow and _ID_ <
> (@StartRow+@PageLength)

> DECLARE mycust SCROLL CURSOR
> FOR SELECT unicode, code, name
> FROM customer
> WHERE unicode LIKE '%'+ ISNULL(@keyword, '') + '%'
> ORDER BY
> CASE @sorting
> WHEN '' THEN unicode
> WHEN 'name' THEN name
> WHEN 'code' THEN code
> ...etc...
> END
>

Uri Dimant wrote:
> Hi
> What is the final result? Can you post DDL+ sample data+ expected result?
> I'm sure it could be done without using a cursor
>

Uri Dimant wrote:
> Hi
> Take a look at http://www.aspfaq.com/show.asp?id=2120