一段优化排序的Sql语句-数据库专栏,SQL Server-数据库其他-idc中文资讯站-客观公证的IDC产业权威媒体,关注域名、虚拟主机、服务器、网络、开源技术资讯，整合运营商资源，客观评价各地机房带宽线路质量、IDC企业名录等信息

一段优化排序的Sql语句-数据库专栏,SQL Server

来源：作者：发布时间:2007-12-25 13:43:09

if exists (select * from dbo.sysobjects where id = object_id(n[dbo].[orderoptimize]) and objectproperty(id, nisprocedure) = 1)
drop procedure [dbo].[orderoptimize]
go

set quoted_identifier on
go
set ansi_nulls off
go

create procedure orderoptimize

(
@id int,
@intorder int,
@tablename varchar(50)
)
as

begin transaction transorderoptimize

declare @sqlstr nvarchar(500)
declare @i int
declare @cursorsql nvarchar (500)
declare @updateorder nvarchar(500)
declare @tempid int
--declare @cursorname varchar(50)
--print(n update +cast(@tablename as varchar(50))+ set intorder = +cast(@intorder as int)+   where id=+@id+);
begin

set @sqlstr=n update +cast(@tablename as varchar(50))+ set intorder = +cast(@intorder as varchar(50))+ where id=+cast(@id as varchar(10))+;

exec sp_executesql @sqlstr;
end

begin
set nocount on
set @i=0;
--set @cursorname=product;
--set @sqltemp=nselect id from +cast(@tablename as varchar(50))+ order by intorder;
--declare order_cursor cursor for sp_executesql @sqltemp

declare @temp nvarchar(500)
set @temp =ndeclare order_cursor cursor for select id from +cast(@tablename as varchar(50))+ order by intorder
exec sp_executesql @temp

open order_cursor
fetch next from order_cursor into @tempid

while @@fetch_status=0
begin
  --print @tempid;
  set @i=@i+1;
  set @updateorder=nupdate +cast(@tablename as varchar(50))+ set intorder=+cast(@i as varchar(10))+ where id=+cast(@tempid as varchar(10))+;
  --print @updateorder;
  execute sp_executesql @updateorder
  fetch next from order_cursor into @tempid
end

close order_cursor
deallocate order_cursor
end

if @@error<>0
begin
raiserror(排序优化失败，请和研发商联系！,16,1)
rollback transaction transorderoptimize
return 99
end

commit transaction transorderoptimize
go
set quoted_identifier off
go
set ansi_nulls on
go

喜欢本文，那就收藏到：