一段优化排序的Sql语句
2007-11-11 09:56:20 来源:WEB开发网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
- ››SQL Server 2008 R2 下如何清理数据库日志文件
- ››sqlite 存取中文的解决方法
- ››SQL2005、2008、2000 清空删除日志
- ››SQL Server 2005和SQL Server 2000数据的相互导入...
- ››sql server 2008 在安装了活动目录以后无法启动服...
- ››sqlserver 每30分自动生成一次
- ››sqlite 数据库 对 BOOL型 数据的插入处理正确用法...
- ››sql server自动生成批量执行SQL脚本的批处理
- ››sql server 2008亿万数据性能优化
- ››SQL Server 2008清空数据库日志方法
- ››sqlserver安装和简单的使用
- ››SQL Sever 2008 R2 数据库管理
更多精彩
赞助商链接