|
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=''''''''''''+@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_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
| 
