一段优化排序的Sql语句

发布: 2007-7-02 11:08 | 作者: admin | 来源: | 查看: 19次 | 进入软件测试论坛讨论

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