软件下载 | 资讯教程 | 最近更新 | 下载排行 | 一键转帖 | 发布投稿
您的位置:最火下载站 > 网络编程 > ASP.NET > Asp.net实现向上向下排序的例子

Asp.net实现向上向下排序的例子

工作中一些常用到的代码记录下来,方便自己查找也方便其他需要人士参考。

废话不多说,这是一个向上向下排序的功能,首先使用存储过程 整好 如下:

SQL:

-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[sp_BannerOrder]
-- Add the parameters for the stored procedure here
(
@tablename nvarchar(50), --表名
@colname nvarchar(50), --排序字段
@keyid nvarchar(50), --表主键字段
@keyidvalue int, --表主键字段值1
@order nvarchar(20), -- 列表默认的排序方式,asc或desc
@orderDirection nvarchar(20), --排序方向,up或down
@where nvarchar(2000) --查询条件
)
AS
BEGIN
declare @ordertmp1 int; --临时排序值id1
declare @ordertmp2 int; --临时排序值id2
declare @tmpkeyidvaule nvarchar(50);
declare @sql nvarchar(2000);
DECLARE @ParmDefinition nvarchar(500);
DECLARE @ParmDefinition2 nvarchar(500);

if @order='asc'
begin
SET @sql = N'SELECT @ordertmp1OUT='+@colname+' from '+@tablename+' where '+@keyid+'='+cast(@keyidvalue as nvarchar(50));
SET @ParmDefinition = N'@ordertmp1OUT nvarchar(20) OUTPUT';
EXECUTE sp_executesql @sql, @ParmDefinition, @ordertmp1OUT=@ordertmp1 OUTPUT;

if @orderDirection='up'
begin
SET @sql = N'SELECT top 1 @ordertmp2OUT='+@colname+',@tmpkeyidvauleOUT='+@keyid+' from '+@tablename+' where '+@colname+'<'+cast(@ordertmp1 as nvarchar(50))+' and '+@where+' order by '+@colname+' desc';
end
else
begin
SET @sql = N'SELECT top 1 @ordertmp2OUT='+@colname+',@tmpkeyidvauleOUT='+@keyid+' from '+@tablename+' where '+@colname+'>'+cast(@ordertmp1 as nvarchar(50))+' and '+@where+' order by '+@colname+' asc';
end

SET @ParmDefinition = N'@ordertmp2OUT nvarchar(20) OUTPUT, @tmpkeyidvauleOUT nvarchar(20) OUTPUT';
EXECUTE sp_executesql @sql, @ParmDefinition, @ordertmp2OUT=@ordertmp2 OUTPUT, @tmpkeyidvauleOUT=@tmpkeyidvaule OUTPUT;
end
else
begin
SET @sql = N'SELECT @ordertmp1OUT='+@colname+' from '+@tablename+' where '+@keyid+'='+cast(@keyidvalue as nvarchar(50));
SET @ParmDefinition = N'@ordertmp1OUT nvarchar(20) OUTPUT';
EXECUTE sp_executesql @sql, @ParmDefinition, @ordertmp1OUT=@ordertmp1 OUTPUT;
if @orderDirection='up'
begin
SET @sql = N'SELECT top 1 @ordertmp2OUT='+@colname+',@tmpkeyidvauleOUT='+@keyid+' from '+@tablename+' where '+@colname+'>'+cast(@ordertmp1 as nvarchar(50))+' and '+@where+' order by '+@colname+' asc';
end
else
begin
SET @sql = N'SELECT top 1 @ordertmp2OUT='+@colname+',@tmpkeyidvauleOUT='+@keyid+' from '+@tablename+' where '+@colname+'<'+cast(@ordertmp1 as nvarchar(50))+' and '+@where+' order by '+@colname+' desc';
end

SET @ParmDefinition = N'@ordertmp2OUT nvarchar(20) OUTPUT, @tmpkeyidvauleOUT nvarchar(20) OUTPUT';
EXECUTE sp_executesql @sql, @ParmDefinition, @ordertmp2OUT=@ordertmp2 OUTPUT, @tmpkeyidvauleOUT=@tmpkeyidvaule OUTPUT;
end

set @sql = 'update '+@tablename+' set '+@colname+'='+cast(@ordertmp2 as nvarchar(50))+' where '+@keyid+'='+cast(@keyidvalue as nvarchar(50));
set @sql = @sql + ' update '+@tablename+ ' set '+@colname+'='+cast(@ordertmp1 as nvarchar(50))+' where '+@keyid+'='+cast(@tmpkeyidvaule as nvarchar(50));

--select @ordertmp1,@ordertmp2,@tmpkeyidvaule,@sql
exec(@sql);
END

相关阅读
网友评论
栏目导航
推荐软件