Dynamic ordering of database query results (4)

Dynamic | data | Database Suppose we add more shippers to the table, as shown in Listing 4 (the ShipperID column has the identity attribute and SQL Server automatically generates a value for that column).



"Listing 4: Insert some records into the Shippers table"


INSERT into Shippers VALUES (' Shipper4 ', ' (111) 222-9999 ')

INSERT into Shippers VALUES (' Shipper5 ', ' (111) 222-8888 ')

INSERT into Shippers VALUES (' Shipper6 ', ' (111) 222-7777 ')

INSERT into Shippers VALUES (' Shipper7 ', ' (111) 222-6666 ')

INSERT into Shippers VALUES (' Shipper8 ', ' (111) 222-5555 ')

INSERT into Shippers VALUES (' Shipper9 ', ' (111) 222-4444 ')

INSERT into Shippers VALUES (' Shipper10 ', ' (111) 222-3333 ')





Now call the stored procedure and specify ShipperID as the row sequence:



EXEC getsortedshippers ' ShipperID '





Table one shows the output of the stored procedure. ShipperID is equal to 10 of the record location error because the sorted output of this stored procedure is a character sort, not an integer sort. When sorted by character, 10 is arranged in front of 2, because 10 starts with 1 characters.


Table One: Record query results for sorting errors


ShipperID CompanyName Phone

1 Speedy Express (503) # 555-9831

Ten Shipper10 (111) # 222-3333

2 United Package (503) 555-3199

3 Federal Shipping (503) 555-9931

4 Shipper4 (111) 222-9999

5 Shipper5 (111) 222-8888

6 Shipper6 (111) 222-7777

7 Shipper7 (111) 222-6666

8 Shipper8 (111) 222-5555

9 Shipper9 (111) 222-4444