SqlServer2000两个存储过程 上下移动 和 临近记录显示

1.上下移动

---------用于设置表中某条数据的顺序。包括记录上移和下移--------
CREATE PROCEDURE [UP_SetOrderID] 
(
@tblName varchar(255),         --表名
@fldName varchar(255),        --主键字段名
@ID int,                --主键字段值
@IsUp bit = 0            --是否上移数据，0表示下移数据，1表示上移
)
AS 

Declare @Sql varchar(2000); 

IF(@IsUp=1) 

    BEGIN 

    Set @Sql=N'Declare @CurrentOrderID varchar(50);Declare @MoveOrderID varchar(50);Declare @MoveID varchar(50);' 

    +N'Select @CurrentOrderID=OrderID From '+@tblName+' where '+@fldName+'='+Cast(@ID as varchar)+';Print @CurrentOrderID;' 

    +N'Select top 1 @MoveID='+@fldName+', @MoveOrderID=OrderID From  '+@tblName+' Where OrderID<@CurrentOrderID order By OrderID desc;' 

    +N'print @MoveID; Print @MoveOrderID;' 

    +N'IF((@MoveID Is Not null) and (@MoveOrderID Is NOT null)) ' 

    +N'BEGIN ' 

    +N'Update '+@tblName +' Set OrderID=@MoveOrderID Where '+@fldName+'='+Cast(@ID as varchar)+';' 

    +N'Update '+@tblName +' Set OrderID=@CurrentOrderID Where '+@fldName+'=@MoveID;' 

    +N'END' 

    Exec (@Sql); 

    Print @Sql; 

    END 

ELSE 

    BEGIN 

    Set @Sql=N'Declare @CurrentOrderID varchar(50);Declare @MoveOrderID varchar(50);Declare @MoveID varchar(50);' 

    +N'Select @CurrentOrderID=OrderID From '+@tblName+' where '+@fldName+'='+Cast(@ID as varchar)+';Print @CurrentOrderID;' 

    +N'Select top 1 @MoveID='+@fldName+', @MoveOrderID=OrderID From  '+@tblName+' Where OrderID>@CurrentOrderID order By OrderID;' 

    +N'print @MoveID; Print @MoveOrderID;' 

    +N'IF((@MoveID Is Not null) and (@MoveOrderID Is NOT null)) ' 

    +N'BEGIN ' 

    +N'Update '+@tblName +' Set OrderID=@MoveOrderID Where '+@fldName+'='+Cast(@ID as varchar)+';' 

    +N'Update '+@tblName +' Set OrderID=@CurrentOrderID Where '+@fldName+'=@MoveID;' 

    +N'END' 

    Exec (@Sql); 

    Print @Sql; 

    END
GO

2.临近记录显示

 

/*
---------用于返回同一栏目下，处于某ID附近（之前或之后）的n条数据。--------
CREATE PROCEDURE [UP_GetRecordOfNearID] 
(
@tblName varchar(255),         --表名
@fieldName varchar(255),    --主键字段名
@fieldNameOthers varchar(255),    --要返回的其他字段，唯一字段，比如标题简介等
@fieldNameOfClass varchar(255),--所属父表主键字段名
@ID int,                --主键字段值
@Size int  = 1,            --返回之前或之后的n条数据，默认为1条
@IsPrev bit = 0            --是否返回之前的数据,默认为返回之后的数据
)
AS
IF @fieldNameOfClass!=''
    BEGIN
    IF(@IsPrev=1)
        Exec('select * from(select distinct top '+@Size +' t1.[' +@fieldName+'], t1.['+ @fieldNameOthers+'] from ' +@tblName+' as t1 where t1.[' + @fieldNameOfClass+']  in (select ['+ @fieldNameOfClass +'] from '+@tblName+' as t2 where t2.['+@fieldName+']=' +@ID+')   and t1.['+@fieldName+']>'+@ID+'  order by t1.['+@fieldName+']) t1 order by t1.['+@fieldName+'] desc ');
    ELSE 
        Exec('select distinct top '+@Size+' t1.[' +@fieldName+'], t1.['+ @fieldNameOthers+'] from ' +@tblName+' as t1 where t1.[' + @fieldNameOfClass+']  in (select ['+ @fieldNameOfClass +'] from '+@tblName+' as t2 where t2.['+@fieldName+']=' +@ID+')  and t1.['+@fieldName+']<' +@ID+'  order by t1.['+@fieldName+'] desc ');
    --print 'select distinct top  '+cast(@Size as varchar(50))+' t1.[' +@fieldName+'], t1.['+ @fieldNameOthers+'] from ' +@tblName+' as t1 where t1.[' + @fieldNameOfClass+']  in (select ['+ @fieldNameOfClass +'] from '+@tblName+' as t2 where t2.['+@fieldName+']=' +cast(@ID as varchar(50))+')  and t1.['+@fieldName+']<' +cast(@ID as varchar(20))+'  order by t1.['+@fieldName+'] desc ';
    END
ELSE    ---在没有父类区分的情况下
BEGIN
    IF(@IsPrev=1)
        Exec('select * from(select distinct top '+@Size +' t1.[' +@fieldName+'], t1.['+ @fieldNameOthers+'] from ' +@tblName+' as t1 where t1.['+@fieldName+']>'+@ID+'  order by t1.['+@fieldName+']) t1 order by t1.['+@fieldName+'] desc ');
    ELSE 
        Exec('select distinct top '+@Size+' t1.[' +@fieldName+'], t1.['+ @fieldNameOthers+'] from ' +@tblName+' as t1 where  t1.['+@fieldName+']<' +@ID+'  order by t1.['+@fieldName+'] desc ');
    END
GO
*/

---------用于返回同一栏目下，处于某ID附近（之前或之后）的n条数据。--------
CREATE PROCEDURE [UP_GetRecordOfNearID] 
(
@tblName varchar(255),         --表名
@fldName varchar(255),    --主键字段名
@fieldNameOthers varchar(255),    --要返回的其他字段，唯一字段，比如标题简介等
@fieldNameOfClass varchar(255),--所属父表主键字段名
@ID int,                --主键字段值
@Size int  = 1,            --返回之前或之后的n条数据，默认为1条
@IsPrev bit = 0            --是否返回之前的数据,默认为返回之后的数据
)
AS
IF @fieldNameOfClass!=''
    BEGIN
    IF(@IsPrev=1)
        Exec('select * from(select distinct top '+@Size +' t1.[' +@fldName+'], t1.['+ @fieldNameOthers+'] from ' +@tblName+' as t1 where t1.[' + @fieldNameOfClass+']  in (select ['+ @fieldNameOfClass +'] from '+@tblName+' as t2 where t2.['+@fldName+']=' +@ID+')   and t1.['+@fldName+']>'+@ID+'  order by t1.['+@fldName+']) t1 order by t1.['+@fldName+'] desc ');
    ELSE 
        Exec('select distinct top '+@Size+' t1.[' +@fldName+'], t1.['+ @fieldNameOthers+'] from ' +@tblName+' as t1 where t1.[' + @fieldNameOfClass+']  in (select ['+ @fieldNameOfClass +'] from '+@tblName+' as t2 where t2.['+@fldName+']=' +@ID+')  and t1.['+@fldName+']<' +@ID+'  order by t1.['+@fldName+'] desc ');
    --print 'select distinct top  '+cast(@Size as varchar(50))+' t1.[' +@fldName+'], t1.['+ @fieldNameOthers+'] from ' +@tblName+' as t1 where t1.[' + @fieldNameOfClass+']  in (select ['+ @fieldNameOfClass +'] from '+@tblName+' as t2 where t2.['+@fldName+']=' +cast(@ID as varchar(50))+')  and t1.['+@fldName+']<' +cast(@ID as varchar(20))+'  order by t1.['+@fldName+'] desc ';
    END
ELSE    ---在没有父类区分的情况下
BEGIN
    IF(@IsPrev=1)
        Exec('select * from(select distinct top '+@Size +' t1.[' +@fldName+'], t1.['+ @fieldNameOthers+'] from ' +@tblName+' as t1 where t1.['+@fldName+']>'+@ID+'  order by t1.['+@fldName+']) t1 order by t1.['+@fldName+'] desc ');
    ELSE 
        Exec('select distinct top '+@Size+' t1.[' +@fldName+'], t1.['+ @fieldNameOthers+'] from ' +@tblName+' as t1 where  t1.['+@fldName+']<' +@ID+'  order by t1.['+@fldName+'] desc ');
    END
GO