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 GOSET ANSI_NULLS OFF GO
CREATE Procedure OrderOptimize
(@ID int,@intOrder int,@TableName varchar(50))AS
BEGIN TRANSACTION TransOrderOptimize
Declare @SqlStr nvarchar(500)Declare @i intDeclare @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=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 @i=@i 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_CursorEnd
if @@error<>0 Begin raiserror('排序优化失败，请与开发商联系！',16,1) RollBack Transaction TransOrderOptimize Return 99end
Commit Transaction TransOrderOptimizeGOSET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS ON GO