存储过程
来源:互联网 发布:java数字转换字符串 编辑:程序博客网 时间:2024/05/21 09:02
CREATE procedure dbo.Pro_GetdudoRireki @tableName varchar(100) ,@tableId int,@oldGetsudoId int,@newGetsudoId int
AS SET NOCOUNT ON
if(@newGetsudoId <> @oldGetsudoId)
begin
declare @minId int, @maxId int, @nextId int, @range int, @intErrorCode int,@intRowcount int, @sql_select nvarchar(400), @sql_insert varchar(400)
, @sql_tempTableName varchar(100) , @sql_tempTableCount int
set @sql_tempTableName = '##tem_GetsudoRireki'
set @sql_tempTableCount = 0
WHILE object_id('tempdb..'+@sql_tempTableName) is not null
begin
set @sql_tempTableCount =@sql_tempTableCount+1
set @sql_tempTableName = '##tem_GetsudoRireki'+STR(@sql_tempTableCount,@sql_tempTableCount/10+1)
end
set @sql_select = 'select * into '+ @sql_tempTableName +' from ' + @tableName + ' where KyuyoGetsudoId = ' + STR(@oldGetsudoId)
exec(@sql_select) set @intRowcount = @@rowcount select @intErrorCode = @@ERROR
if @intRowcount <> 0
begin
set @sql_select = N'select @minId = min('+@sql_tempTableName+'.Id) from '+ @sql_tempTableName
exec sp_executesql @sql_select,N'@minId int output',@minId output
set @sql_select = N'select @maxId = max('+@sql_tempTableName+'.Id) from '+ @sql_tempTableName
exec sp_executesql @sql_select,N'@maxId int output',@maxId output
exec @nextId = pro_GetSerialNextId @tableId
set @range = (@nextId - @minId + @maxId + 1)
exec pro_SetSerialNextId @tableId, @range
set @sql_select = 'update ' + @sql_tempTableName +' set Id = '+ @sql_tempTableName+'.Id +'+STR(@nextId)+'-'+STR(@minId)+
',KyuyoGetsudoId = '+ STR(@newGetsudoId)
exec(@sql_select)
set @sql_insert = 'insert into ' + @tableName + ' select * from '+ @sql_tempTableName
exec(@sql_insert)
end
set @sql_select = 'drop table '+ @sql_tempTableName
exec(@sql_select)
return @@ERROR
end
a globally unique identifier
uniqueidentifier
uniqueidentifier 列的 GUID 值通常由以下方式获得:
在 Transact-SQL 语句、批处理或脚本中调用 NEWID 函数。
在应用程序代码中,调用返回 GUID 值的应用程序 API 函数或方法。
Transact-SQL NEWID 函数以及应用程序 API 函数和方法从它们网卡上的标识数字以及 CPU 时钟的唯一数字生成新的 uniqueidentifier 值。每个网卡都有唯一的标识号。由 NEWID 返回的 uniqueidentifier 使用服务器上的网卡生成。由应用程序 API 函数和方法返回的 uniqueidentifier 使用客户机上的网卡生成。
DECLARE @myid uniqueidentifier
SET @myid = NEWID()
PRINT 'Value of @myid is: '+ CONVERT(varchar(255), @myid)
//
ALTER procedure dbo.Pro_GetdudoRireki @tableName varchar(100) ,@tableId int,@oldGetsudoId int,@newGetsudoId int AS
SET NOCOUNT ON
if(@newGetsudoId <> @oldGetsudoId)
begin
declare @minId int, @maxId int, @nextId int, @range int, @intErrorCode int,@intRowcount int, @sql_select nvarchar(400), @sql_insert varchar(400) , @sql_tempTableName varchar(100) , @sql_tempTableCount int
DECLARE @myid uniqueidentifier
SET @myid = NEWID()
PRINT 'Value of @myid is: '+ CONVERT(varchar(255), @myid)
set @sql_tempTableName = '[##temGR_'+CONVERT(varchar(255), @myid)+']'
print @sql_tempTableName
set @sql_select = 'select * into '+ @sql_tempTableName +' from ' + @tableName + ' where KyuyoGetsudoId = ' + STR(@oldGetsudoId)
exec(@sql_select)
set @intRowcount = @@rowcount select @intErrorCode = @@ERROR
if @intRowcount <> 0
begin
set @sql_select = N'select @minId = min('+@sql_tempTableName+'.Id) from '+ @sql_tempTableName
exec sp_executesql @sql_select,N'@minId int output',@minId output
set @sql_select = N'select @maxId = max('+@sql_tempTableName+'.Id) from '+ @sql_tempTableName
exec sp_executesql @sql_select,N'@maxId int output',@maxId output
exec @nextId = pro_GetSerialNextId @tableId
set @range = (@nextId - @minId + @maxId + 1)
exec pro_SetSerialNextId @tableId, @range
set @sql_select = 'update ' + @sql_tempTableName +' set Id = '+ @sql_tempTableName+'.Id +'+STR(@nextId)+'-'+STR(@minId)+ ',KyuyoGetsudoId = '+ STR(@newGetsudoId)
exec(@sql_select)
set @sql_insert = 'insert into ' + @tableName + ' select * from '+ @sql_tempTableName
exec(@sql_insert)
end
set @sql_select = 'drop table '+ @sql_tempTableName
exec(@sql_select) return @@ERROR
end