一段最佳化排序的Sql語句

來源:互聯網
上載者:User
排序|最佳化|語句
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

 


相關文章

Cloud Intelligence Leading the Digital Future

Alibaba Cloud ACtivate Online Conference, Nov. 20th & 21st, 2019 (UTC+08)

Register Now >

Starter Package

SSD Cloud server and data transfer for only $2.50 a month

Get Started >

Alibaba Cloud Free Trial

Learn and experience the power of Alibaba Cloud with a free trial worth $300-1200 USD

Learn more >

聯繫我們

該頁面正文內容均來源於網絡整理,並不代表阿里雲官方的觀點,該頁面所提到的產品和服務也與阿里云無關,如果該頁面內容對您造成了困擾,歡迎寫郵件給我們,收到郵件我們將在5個工作日內處理。

如果您發現本社區中有涉嫌抄襲的內容,歡迎發送郵件至: info-contact@alibabacloud.com 進行舉報並提供相關證據,工作人員會在 5 個工作天內聯絡您,一經查實,本站將立刻刪除涉嫌侵權內容。