Could someone please help with a stored procedure to accomplish the following:<br>
Table: Fixit.dbo.fixits
<table width="100%" border="1">
<tr>
<td>ID</td>
<td>Fixit_Id</td>
<td>Name</td>
<td>Sequence</td>
</tr>
<tr>
<td>3257</td>
<td>123</td>
<td>Name1</td>
<td>1</td>
</tr>
<tr>
<td>3258</td>
<td>123</td>
<td>Name2</td>
<td>2</td>
</tr>
<tr>
<td>3259</td>
<td>123</td>
<td>Name3</td>
<td>3</td>
</tr>
</table>
On the page there is an "up" arrow and a "down" arrow associated
with each record to be used as triggers.<br>
Clicking "up" arrow:
Using record #2 as the reference point I would like to "move up" to
replace record #1, but have the uniqueID remain in place.
Result = all data (except uniqueID) in record #2 swaps places with all data
(except uniqueID) in record #1.<br>
Clicking "down" arrow
Likewise I would like to "move down" to replace record #3, but have
the uniqueID remain in place.
Result = all data (except uniqueID) in record #2 swaps places with all data
(except uniqueID) in record #3.<br>
Any help would be greatly appreciated!<br>
Thanks,
John

hmmmm ... no html I guess?

Could someone please help with a stored procedure to accomplish the following:

Table: Fixit.dbo.fixits

ID Name Fixit_Id Sequence
----------------------------------------
3257 Name1 123 1
3258 Name2 123 2
3259 Name3 123 3
etc. etc. etc. etc.

On the page there is an "up" arrow and a "down" arrow associated with each record to be used as triggers. Clicking "up" arrow:
Using record #2 as the reference point I would like to "move up" to replace record #1, but have the uniqueID remain in place.
Result = all data (except uniqueID) in record #2 swaps places with all data (except uniqueID) in record #1

Clicking "down" arrow
Likewise I would like to "move down" to replace record #3, but have the uniqueID remain in place.
Result = all data (except uniqueID) in record #2 swaps places with all data (except uniqueID) in record #3

Any help would be greatly appreciated!

Thanks,
John

I hope this is better

Try this:

-------------------------------------------------------------------------------------------------
CREATE PROCEDURE spSwapRecord

@RecID int,
@Direction varchar(4)

AS

SET NOCOUNT ON

DECLARE @ID1 int;
DECLARE @name1 varchar(20);
DECLARE @Fixit_Id1 int;
DECLARE @Sequence1 int;
DECLARE @ID2 int;
DECLARE @name2 varchar(20);
DECLARE @Fixit_Id2 int;
DECLARE @Sequence2 int;

IF @Direction = 'PREV'

BEGIN

DECLARE swap_cursor SCROLL CURSOR FOR
SELECT ID, name, Fixit_Id,Sequence FROM fixits
WHERE ID <= @RecID

OPEN swap_cursor

FETCH ABSOLUTE -1 FROM swap_cursor
INTO @ID1, @name1, @Fixit_Id1, @Sequence1

FETCH ABSOLUTE -2 FROM swap_cursor
INTO @ID2, @name2, @Fixit_Id2, @Sequence2

END

ELSE

BEGIN

DECLARE swap_cursor SCROLL CURSOR FOR
SELECT ID, name, Fixit_Id,Sequence FROM fixits
WHERE ID >= @RecID

OPEN swap_cursor

FETCH ABSOLUTE 1 FROM swap_cursor
INTO @ID1, @name1, @Fixit_Id1, @Sequence1

FETCH ABSOLUTE 2 FROM swap_cursor
INTO @ID2, @name2, @Fixit_Id2, @Sequence2

END

CLOSE swap_cursor
DEALLOCATE swap_cursor

UPDATE fixits SET name = @name1, Fixit_Id = @Fixit_Id1, Sequence = @Sequence1 WHERE ID = @ID2

UPDATE fixits SET name = @name2, Fixit_Id = @Fixit_Id2, Sequence = @Sequence2 WHERE ID = @ID1

SET NOCOUNT OFF
GO
-------------------------------------------------------------------------------------------------

Regards

Owen.

Multiple Parameter UD4 / Access 2000 Database Search Tutorial:
www.tdsf.co.uk/tdsfdemo

Edited by - oeastwick on 09 Dec 2002 08:06:03