MSSQL:
存储过程
-- drop PROCEDURE [dbo].[Proc_sort_UpDown]
create PROCEDURE [dbo].[Proc_sort_UpDown]
@Sign int = 0, -- 0: 上移 1:下移
@TableName nvarchar(50), <span style="white-space:pre"> </span>-- 表名
@ItemName nvarchar(50), <span style="white-space:pre"> </span>-- 主键字段名
@ItemID int, <span style="white-space:pre"> </span>-- 被移动的主键ID
@SortName nvarchar(50), <span style="white-space:pre"> </span>-- 排序ID
@TypeName nvarchar(50)='', <span style="white-space:pre"> </span>-- 分类字段名
@TypeValue nvarchar(50)= '' <span style="white-space:pre"> </span>-- 分类值
AS
BEGIN
SET NOCOUNT ON
DECLARE
@SQL nvarchar(4000),
@ThisSort int, -- 当前ID
@PREVID int, -- 前一个ID
@NextID int, -- 后一个ID
@Count int
--临时索引表--
CREATE TABLE #Tab
(
ItemID int,
Sort int
)
SET @SQL = 'INSERT INTO #Tab (ItemID,Sort) SELECT '
+@ItemName+','+@SortName+' FROM '+@TableName
--INSERT INTO #Tab (ItemID,Sort) SELECT AutoID,sortID FROM test
IF (@TypeName<>'' AND @TypeValue<>'') SET @SQL = @SQL+' WHERE '+@TypeName+'='+@TypeValue
SET @SQL = @SQL+' ORDER BY '+@SortName+' ASC '
--INSERT INTO #Tab (ItemID,Sort) SELECT AutoID,sortID FROM test WHERE lang=cn ORDER BY SortID ASC
EXEC(@SQL)
SET @SQL = ''
SELECT @Count = COUNT(*) FROM #Tab
SELECT @ThisSort = Sort FROM #Tab WHERE ItemID = @ItemID
IF(@ThisSort>1) SELECT @PREVID = ItemID FROM #Tab WHERE Sort=(@ThisSort-1)
IF(@ThisSort<@Count) SELECT @NextID = ItemID FROM #Tab WHERE Sort=(@ThisSort+1)
IF(@Sign=0)
BEGIN
IF(@ThisSort>1)
BEGIN
SET @SQL = 'UPDATE '+@TableName+' SET '+@SortName+'='+CONVERT(varchar(100),(@ThisSort-1))+' WHERE '+@ItemName+' = '+CONVERT(varchar(100),@ItemID) + ';'
+ 'UPDATE '+@TableName+' SET '+@SortName+'='+CONVERT(varchar(100),@ThisSort)+' WHERE '+@ItemName+' = '+CONVERT(varchar(100),@PREVID)
END
END
ELSE
BEGIN
IF(@ThisSort<@Count)
BEGIN
SET @SQL = 'UPDATE '+@TableName+' SET '+@SortName+'='+CONVERT(varchar(100),(@ThisSort+1))+' WHERE '+@ItemName+' = '+CONVERT(varchar(100),@ItemID) + ';'
+ 'UPDATE '+@TableName+' SET '+@SortName+'='+CONVERT(varchar(100),@ThisSort)+' WHERE '+@ItemName+' = '+CONVERT(varchar(100),@NextID)
END
END
EXEC(@SQL)
END
测试表
CREATE TABLE [dbo].[test](
[AutoID] [int] IDENTITY(1,1) NOT NULL,
[cName] [varchar](50) NULL,
[sortID] [int] NULL,
CONSTRAINT [PK_test] PRIMARY KEY CLUSTERED
(
[AutoID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
-- 测试数据
INSERT INTO [Demo].[dbo].[test]
([cName]
,[sortID])
VALUES
('JAVA',1),
('C++',2),
('C#',3),
('Object-C',4),
('PHP',5),
('Ruby',6),
('Python',7)
GO
应用操作->存储过程执行
DECLARE @RC int
DECLARE @Sign int
DECLARE @TableName nvarchar(50)
DECLARE @ItemName nvarchar(50)
DECLARE @ItemID int
DECLARE @SortName nvarchar(50)
DECLARE @TypeName nvarchar(50)
DECLARE @TypeValue nvarchar(50)
select @Sign=0
select @TableName='test'
select @ItemName='AutoID'
select @ItemID=6
select @SortName='sortID'
select @TypeName=''
select @TypeValue=''
EXECUTE @RC = [Demo].[dbo].[Proc_sort_UpDown]
@Sign
,@TableName
,@ItemName
,@ItemID
,@SortName
,@TypeName
,@TypeValue
GO