代码实现很简单,跟据传入的记录ID,参数 up,down,top,eof就可以实现上移,下移,置顶,置尾的功能了!!
上移:本记录与上条记录的序号字段进行交换,如果为第一条记录提示错误
下移:本记录与下条记录的序号字段进行交换,如果为最后一条记录提示错误
置顶:本记录置为1,如果已为第一条提示否则小于本记录序号的记录全部+1
置尾:本记录置为序号的最大值,如果已是最大值返回提示否则置为最大值,大于它的记录全部-1
注:使用本过程还要注意删除记录的时候同时要把大于被删除记录序号的字段全部-1,这样才能保证记录的连续性!
思路简单,性能低下呵呵!
CREATE
PROCEDURE
AdminClass_MoveMingzhan(
@nID
int
,
@Moves
varchar
(
20
),
@OutId
int
OUTPUT)
AS
IF @Moves = ' up '
IF ( SELECT Mz_Top FROM Dh_Mingzhan WHERE Mz_ID = @nID ) - 1 = 0
SET @OutId = 10001
ELSE
BEGIN
UPDATE Dh_Mingzhan SET Mz_Top = Mz_Top + 1 WHERE Mz_Top = ( SELECT Mz_Top FROM Dh_Mingzhan WHERE Mz_ID = @nID ) - 1
UPDATE Dh_Mingzhan SET Mz_Top = Mz_Top - 1 WHERE Mz_ID = @nID
SET @OutId = 100
END
IF @Moves = ' down '
IF ( SELECT Mz_Top FROM Dh_Mingzhan WHERE Mz_ID = @nID ) = ( SELECT MAX (Mz_Top) FROM Dh_Mingzhan)
SET @OutId = 10002
ELSE
BEGIN
UPDATE Dh_Mingzhan SET Mz_Top = Mz_Top - 1 WHERE Mz_Top = ( SELECT Mz_Top FROM Dh_Mingzhan WHERE Mz_ID = @nID ) + 1
UPDATE Dh_Mingzhan SET Mz_Top = Mz_Top + 1 WHERE Mz_ID = @nID
SET @OutId = 100
END
IF @Moves = ' top '
IF ( SELECT Mz_Top FROM Dh_Mingzhan WHERE Mz_ID = @nID ) = 1
SET @OutId = 10003
ELSE
BEGIN
UPDATE Dh_Mingzhan SET Mz_Top = Mz_Top + 1 WHERE Mz_Top < ( SELECT Mz_Top FROM Dh_Mingzhan WHERE Mz_ID = @nID )
UPDATE Dh_Mingzhan SET Mz_Top = 1 WHERE Mz_ID = @nID
SET @OutId = 100
END
IF @Moves = ' eof '
IF ( SELECT Mz_Top FROM Dh_Mingzhan WHERE Mz_ID = @nID ) = ( SELECT MAX (Mz_Top) FROM DH_Mingzhan)
SET @OutId = 10004
ELSE
BEGIN
DECLARE @MaxTop INT
SET @MaxTop = ( SELECT MAX (Mz_Top) FROM Dh_Mingzhan)
UPDATE Dh_Mingzhan SET Mz_Top = Mz_Top - 1 WHERE Mz_Top > ( SELECT Mz_Top FROM Dh_Mingzhan WHERE Mz_ID = @nID )
UPDATE Dh_Mingzhan SET Mz_Top = @MaxTop WHERE Mz_ID = @nID
SET @OutId = 100
END
GO
AS
IF @Moves = ' up '
IF ( SELECT Mz_Top FROM Dh_Mingzhan WHERE Mz_ID = @nID ) - 1 = 0
SET @OutId = 10001
ELSE
BEGIN
UPDATE Dh_Mingzhan SET Mz_Top = Mz_Top + 1 WHERE Mz_Top = ( SELECT Mz_Top FROM Dh_Mingzhan WHERE Mz_ID = @nID ) - 1
UPDATE Dh_Mingzhan SET Mz_Top = Mz_Top - 1 WHERE Mz_ID = @nID
SET @OutId = 100
END
IF @Moves = ' down '
IF ( SELECT Mz_Top FROM Dh_Mingzhan WHERE Mz_ID = @nID ) = ( SELECT MAX (Mz_Top) FROM Dh_Mingzhan)
SET @OutId = 10002
ELSE
BEGIN
UPDATE Dh_Mingzhan SET Mz_Top = Mz_Top - 1 WHERE Mz_Top = ( SELECT Mz_Top FROM Dh_Mingzhan WHERE Mz_ID = @nID ) + 1
UPDATE Dh_Mingzhan SET Mz_Top = Mz_Top + 1 WHERE Mz_ID = @nID
SET @OutId = 100
END
IF @Moves = ' top '
IF ( SELECT Mz_Top FROM Dh_Mingzhan WHERE Mz_ID = @nID ) = 1
SET @OutId = 10003
ELSE
BEGIN
UPDATE Dh_Mingzhan SET Mz_Top = Mz_Top + 1 WHERE Mz_Top < ( SELECT Mz_Top FROM Dh_Mingzhan WHERE Mz_ID = @nID )
UPDATE Dh_Mingzhan SET Mz_Top = 1 WHERE Mz_ID = @nID
SET @OutId = 100
END
IF @Moves = ' eof '
IF ( SELECT Mz_Top FROM Dh_Mingzhan WHERE Mz_ID = @nID ) = ( SELECT MAX (Mz_Top) FROM DH_Mingzhan)
SET @OutId = 10004
ELSE
BEGIN
DECLARE @MaxTop INT
SET @MaxTop = ( SELECT MAX (Mz_Top) FROM Dh_Mingzhan)
UPDATE Dh_Mingzhan SET Mz_Top = Mz_Top - 1 WHERE Mz_Top > ( SELECT Mz_Top FROM Dh_Mingzhan WHERE Mz_ID = @nID )
UPDATE Dh_Mingzhan SET Mz_Top = @MaxTop WHERE Mz_ID = @nID
SET @OutId = 100
END
GO