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

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

转载于:https://www.cnblogs.com/windthunder/archive/2009/12/25/1632244.html