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 S p_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 ( 'arrangement optimization Failure, please contact the developer! ', 16, 1) Rollback Transaction TransorderOptimize Return 99nd Commit Transaction TransorderOptimizegoset Quote_Identifier Off Goset Ansi_nulls On Go
