|
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
[办公软件]在sybase中插入图片、PDF、文本文件 [办公软件]安装Sybase ASE
[办公软件]linux指令大全(完整篇) [办公软件]Linux新手入门常用命令大全
[办公软件]在RedHat Linux 9里安装gaim0.80 [办公软件]浅谈Linux 下Java 1.5 汉字方块问题解决方法
[办公软件]Linux程序员必读：中文化与GB18030标准 [办公软件]linux指令大全
[办公软件]制作Linux启动盘的四种方法 [办公软件]Linux文件系统的反删除方法
| 
注：本站部分文章源于互联网，版权归原作者所有！如有侵权，请原作者与本站联系，本站将立即删除！ 本站文章除特别注明外均可转载，但需注明出处！ [MinTao学以致用网] 
