一段优化排序的Sql语句
2024-07-21 02:06:12
供稿：网友
，欢迎访问网页设计爱好者web开发。
if exists (select * from dbo.sysobjects where id = object_id(n'[dbo].[orderoptimize]') and objectproperty(id, n'isprocedure') = 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='''[email protected]+'''');
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=n'select id from '+cast(@tablename as varchar(50))+' order by intorder';
--declare order_cursor cursor for sp_executesql @sqltemp
declare @temp nvarchar(500)
set @temp =n'declare 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 @[email protected]+1;
set @updateorder=n'update '+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
