程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> SqlServer數據庫 >> 關於SqlServer >> 一段優化排序的Sql語句

一段優化排序的Sql語句

編輯:關於SqlServer

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

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved