Re: Selcting the alpabetical next row

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Aaron Bertrand [MVP] (aaron_at_TRASHaspfaq.com)
Date: 05/28/04 

Date: Fri, 28 May 2004 18:57:23 -0400

Some potential examples here, you could set the TOP 10 to 1, for example, in
the "more challenging" sample.

http://www.aspfaq.com/2352 

-- 
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Olav" <anonymous@discussions.microsoft.com> wrote in message 
news:0ABA02A2-CEB9-4B26-A251-9A5F269C407A@microsoft.com...
> Hi,
> I have a customer database where i display 1 customer at a time with all 
> the customer info. I have a next button so that the user can display the 
> next customer sorted in the following order:
>
> Last name
> First name
> Customerid
>
> So, if lastname is equal it is sorted on firstname and if lastname and 
> firstname is equal it is sorted on customerid
> The procedure for this look like this:
>
> CREATE  PROCEDURE dbo.SearchCustomerNext
> @customerid int,
> @lastname nvarchar(50),
> @firstname nvarchar(50)
> AS
> DECLARE
> @fullname nvarchar(115),
> @namestr nvarchar(115)
> SELECT @fullname = RTRIM(@lastname) + ' ' + RTRIM(@firstname) + ' ' + 
> CONVERT( nvarchar, 1000000000 + @customerid)
>
> SELECT @namestr = min(RTRIM(lastname) + ' ' + RTRIM(firstname) + ' ' + 
> CONVERT( nvarchar, 1000000000 + customerid))
> FROM Customer
> WHERE
> RTRIM(lastname) + ' ' + RTRIM(firstname) + ' ' + CONVERT( nvarchar, 
> 1000000000 + customerid) > @fullname
> IF ( LEN( @namestr) > 0)
> BEGIN
> SELECT *
> FROM Customer
> WHERE customerid = CONVERT( int, RIGHT( @namestr, 10)) - 1000000000
> END
> RETURN
>
>
>
Quantcast