Using a Stored Procedure to sort and update a column

Using a Stored Procedure to sort and update a column

Post by Jim Bancrof » Thu, 11 Jul 2002 05:55:45



I have a table I'd like to keep ordered, with no gaps in one of the columns.
Let's say it's a three-column table and looks like this:

OptionID            OptionOrder               OptionLabel
---------             -------------                ------------

4453                    1                                Some text here
5532                    2                                More text here (not
a large field)
8811                    3                                Still More text
6623                    4                                etc
7611                    4                                etc
2187                    5                                etc

What I'm looking to do first is select the columns, sorted by OptionOrder
and OptionLabel.  Then, I want to check to see that the same OptionOrder
isn't listed twice-- so that for instance, there won't be any duplicate "4"s
above.

In other words, I'd like to keep the ordering the way it is, but update the
OptionOrder column so that it's sequential, with no duplicates. (and
preferably, no gaps either)  If someone could point me in the right
direction on this I'd be grateful, and thanks.

 
 
 

Using a Stored Procedure to sort and update a column

Post by Anith Se » Thu, 11 Jul 2002 06:12:19


Try the following...

SELECT
    OptionId,
    IDENTITY(int, 1,1) AS OptionOrder,
    OptionLabel
INTO NewTable
FROM anotherTable
ORDER BY
    OptionOrder,
    OptionLabel
GO
DROP TABLE anotherTable
GO
sp_Rename NewTable, anotherTable
GO
SELECT * FROM anotherTable

- Anith


Quote:> I have a table I'd like to keep ordered, with no gaps in one of the
columns.
> Let's say it's a three-column table and looks like this:

> OptionID            OptionOrder               OptionLabel
> ---------             -------------                ------------

> 4453                    1                                Some text here
> 5532                    2                                More text here
(not
> a large field)
> 8811                    3                                Still More text
> 6623                    4                                etc
> 7611                    4                                etc
> 2187                    5                                etc

> What I'm looking to do first is select the columns, sorted by OptionOrder
> and OptionLabel.  Then, I want to check to see that the same OptionOrder
> isn't listed twice-- so that for instance, there won't be any duplicate
"4"s
> above.

> In other words, I'd like to keep the ordering the way it is, but update
the
> OptionOrder column so that it's sequential, with no duplicates. (and
> preferably, no gaps either)  If someone could point me in the right
> direction on this I'd be grateful, and thanks.


 
 
 

Using a Stored Procedure to sort and update a column

Post by Mikhail Berlyan » Thu, 11 Jul 2002 06:19:14


create table #TempTable (OptionID int, OptionOrder int IDENTITY(1,1))

insert #TempTable (OptionID)
select OptionID
from YourTable
order by OptionOrder, OptionLabel

update a
set a.OptionOrder = b.OptionOrder
from YourTable a
join #TempTable b
on a.OptionID = b.OptionID

drop table #TempTable

Mikhail Berlyant
Data Integrator, Data Systems
Launch Your Yahoo!Music Experience  http://launch.yahoo.com
Brainbench MVP for Visual Basic   www.brainbench.com


Quote:> I have a table I'd like to keep ordered, with no gaps in one of the
columns.
> Let's say it's a three-column table and looks like this:

> OptionID            OptionOrder               OptionLabel
> ---------             -------------                ------------

> 4453                    1                                Some text here
> 5532                    2                                More text here
(not
> a large field)
> 8811                    3                                Still More text
> 6623                    4                                etc
> 7611                    4                                etc
> 2187                    5                                etc

> What I'm looking to do first is select the columns, sorted by OptionOrder
> and OptionLabel.  Then, I want to check to see that the same OptionOrder
> isn't listed twice-- so that for instance, there won't be any duplicate
"4"s
> above.

> In other words, I'd like to keep the ordering the way it is, but update
the
> OptionOrder column so that it's sequential, with no duplicates. (and
> preferably, no gaps either)  If someone could point me in the right
> direction on this I'd be grateful, and thanks.

 
 
 

1. Retrieving output parameters AND updating image/text column from single stored procedure using DBLibrary

Hi,

I have written a stored procedure that uses several OUTPUT parameters as
well as (sometimes) selects a single row of an image column.

I am attempting to execute this stored procedure, retrieve the output
parameters, and update the selected image column in C/C++ using the
DBLibrary interface.

My problem is that when the stored procedure successfully selects an
image column (always a single row), I can not retrieve the output
parameters.  Conversely, if the stored procedure does not select the
image column, I have no problem retreiving the output parameters.

The following are the calls made to the DBLibrary interface and the
return values in the case where the stored procedure selects an image
column.

dbrpcinit() -- returns SUCCEED
dbrpcparam() (several of these) -- returns SUCCEED
dbrpcexec() -- returns SUCCEED
dbsqlok() - returns SUCCEED
dbresults() -- returns SUCCEED
dbnextrow() -- returns REG_ROW
dbtxptr() -- returns a valid text pointer
dbtxtimestamp() -- returns a valid text time stamp
dbnextrow() -- returns NO_MORE_ROWS
dbresults() -- returns NO_MORE_RESULTS
dbwritetext() -- returns SUCCEED
dbnextrow() -- returns NO_MORE_ROWS
dbretdata() (several of these) -- returns FAILED

The following are the calls made to the DBLibrary interface and the
return values in the case of the stored procedure failing to select an
image column:

dbrpcinit() -- returns SUCCEED
dbrpcparam() (several of these) -- returns SUCCEED
dbrpcexec() -- returns SUCCEED
dbsqlok() - returns SUCCEED
dbresults() -- returns SUCCEED
dbnextrow() -- returns NO_MORE_ROWS
dbretdata() (several of these) -- returns SUCCEED

In both cases, the stored procedure is being executed correctly, with
input parameters being passed in correctly.  In the selected image
column case, the image column is correctly updated in the table; in the
non-selected image column case, the output parameters are being
correctingly retrieved.

Does anyone know how to do this correctly or know any pointers to
information about this?  I can find no detailed info about this in the
MS SQL Server Books Online and the book _Microsoft SQL Server 6.5
Unleashed_.

Oh yeah, I am using version 6.50.213 (This is what is reported by the
dbinit(), as well as by the MS SQL Enterprise Manager Server
Configuration/Options dialog (Attributes tab)).

I can think of several workarounds, but they involve extra communication
with the SQL Server, which I am trying to avoid.  Also, it might be
interesting to note that if I read the image column instead of update
it, I have no problem retreiving the output parameters.

Thanks for any help/info.

--

Hitachi Computer Products (America)     phone:  781-890-0444
Waltham, MA  02154                      fax:    781-890-4998

2. New in MDX. Please help!

3. Updating a datetime column using a stored procedure

4. Print Orientation PDox5 under Win95

5. SORT BY column# in stored procedure

6. Importing Data

7. Sorting Stored procedure using a parameter

8. Help on DBCombBox component

9. Q:Sorting A Grid Bound To A Ado DataControl Using A Parameter'd Stored Procedure

10. Help How To Sort A Stored Procedure Using The Ado Data Control

11. Stored Procedure that Updates a varying Column

12. Stored Procedure to automatically rename directory and update Table/Column

13. Update text column in stored procedure?