葡京网投哪个正规 > 新葡亰-数据 > mssqlserver中最大ID冲突解决办法,通过存储过程动态操作表中的列

原标题:mssqlserver中最大ID冲突解决办法,通过存储过程动态操作表中的列

浏览次数:97 时间:2020-05-07

本小说介绍了关于mssqlserver中最大ID矛盾解决办法,有亟待的对象能够参照他事他说加以考察一下。

复制代码 代码如下: CREATE TABLE [dbo].[TbGuidTable]( [TableName] [varchar](50) NOT NULL, [KeyName] [varchar](20) NOT NULL, [KeyValue] [varchar](100) NOT NULL ) ON [PRIMARY] GO if exists (select * from dbo.sysobjects where id = object_id(N'PCreateGuid'卡塔尔 and OBJECTPROPERTY(id, N'IsProcedure'State of Qatar = 1卡塔尔国 drop procedure PCreateGuid GO ------------------------------------ --用场:扩充一条记录 个人音讯 --项目名称: --表达: --时间:二零一二-7-4 15:34:12,贰零壹贰-08-15 ------------------------------------ CREATE PROCEDURE PCreateGuid @tableName varchar(50State of Qatar, @keyname varchar(100State of Qatar, @guid varchar(100卡塔尔(قطر‎ out as if not exists(select * from TbGuidTable where tablename=@tableName and keyname=@keyname) insert into TbGuidTable (tablename,keyname,keyvalue) values(@tableName,@keyname,10000) update TbGuidTable set keyvalue=keyvalue+1 where tablename=@tableName and keyname=@keyname select @guid=cast(keyvalue as varchar) from TbGuidTable where tablename=@tableName and keyname=@keyname Go ---*****--- declare @tableName varchar(50) declare @keyname varchar(100) declare @guid varchar(100) set @tableName='tb1' set @keyname='def' execute PCreateGuid @tableName,@keyname,@guid out print @guid


1 增添字段 复制代码 代码如下: IF NOT EXISTS (SELECT * FROM SYSCOLUMNS WHERE ID=OBJECT_ID('QueryDataSource')and name = 'IsCrossTable'卡塔尔 BEGIN ALTETiggo TABLE QueryDataSource ADD IsCrossTable bit default(0卡塔尔 --插入字段 END 2 存款和储蓄进程 复制代码 代码如下: if exists (select * from dbo.sysobjects where id = object_mssqlserver中最大ID冲突解决办法,通过存储过程动态操作表中的列。id(N'[dbo].[PLSystem_Insert]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[PLSystem_Insert] go CREATE PROCEDURE dbo.PLSystem_Insert ( @PLSystemID smallint, @PLSystemName varchar(20), @PLSystemFlag char(2) ) AS INSERT INTO dbo.PLSystem ( PLSystemID, PLSystemName, PLSystemFlag ) VALUES ( @PLSystemID, @PLSystemName, @PLSystemFlag ) go if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[PLSystem_Delete]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[PLSystem_Delete] go CREATE PROCEDURE dbo.PLSystem_Delete ( @PLSystemID smallint ) AS DELETE FROM dbo.PLSystem WHERE PLSystemID = @PLSystemID go if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[PLSystem_Update]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[PLSystem_Update] go CREATE PROCEDURE dbo.PLSystem_Update ( @PLSystemID smallint, @PLSystemName varchar(20卡塔尔国, @PLSystemFlag char(2State of Qatar 卡塔尔国 AS UPDATEdbo.PLSystem SET PLSystemName = @PLSystemName, PLSystemFlag = @PLSystemFlag WHERE PLSystemID = @PLSystemID go 3 创制表 复制代码 代码如下: IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[PLSystem]') AND type in (N'U')) DROP TABLE [dbo].[PLSystem] GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[PLSystem]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[PLSystem]( [PLSystemID] [tinyint] NOT NULL, [PLSystemName] [varchar](20) NULL, [PLSystemFlag] [char](2) NULL, CONSTRAINT [XPKPLSystem] PRIMARY KEY CLUSTERED ( [PLSystemID] ASC ) ) ON [PRIMARY] END GO 4 游标 复制代码 代码如下: Begin declare @PluserID varchar(8卡塔尔(قطر‎ declare pluserCurse Cursor for select PLUserID from PLUser where PCancelJudge=0 open pluserCurse --展开游标 fetch next from pluserCurse into @PluserID while @@FETCH_STATUS=0 begin print (@PluserID卡塔尔(قطر‎ insert into PLUserActorDepart(DepartID,PLActorSystemID,PLUserID卡塔尔 select DepartmentID,PLSystemID,PLUserID from MSUserRight where MSUserRight.DepartmentID not in ( select DepartmentID from PLDepartment where PLDepartment.DParent=(select DepartmentID from PLUser where PLUserID=@PluserID 卡塔尔 O中华V PLDepartment.DepartmentID=(select DepartmentID from PLUser where PLUserID =@PluserID卡塔尔(قطر‎ State of Qatar AND MSUserRight.PLUserID=@PluserID fetch next from pluserCurse into @PluserID end close pluserCurse deallocate pluserCurse end 5 用while落成for循环 复制代码 代码如下: Create Proc InsertSQL AS Begin DECLARE @Count int set @Count=0; While(@Count200300卡塔尔 Begin Print(@Count卡塔尔(قطر‎; Insert into Student values('YOUNG','M',100,'FUJIANXIAMENT'卡塔尔(قطر‎; set @Count=@Count+1; END END

在多个一定的表中保存最大Id,通过记录来取每一次的有一无二最大值。

/*应用办法

代码如下复制代码

1.添加列
 exec pro_AddColumn 表名,列名,类型/属性,''
 参数表
 @tableName varchar(100),
 @columNname varchar(100),
 @DbType varchar(32),
 @Msg varchar(50) output
2.修改 
 exec pro_AddColumn 表名,列名,类型/属性,''
 参数表
 @tableName varchar(100),
 @columNname varchar(100),
 @DbType varchar(32),
 @Msg varchar(50) output
 
3.删除
 exec pro_AddColumn 表名,列名
 参数表
 @tableName varchar(100),
 @columNname varchar(100),
 @Msg varchar(50) output

CREATE TABLE [葡京网投哪个正规,dbo].[TbGuidTable]( [TableName] [varchar](50) NOT NULL, [KeyName] [varchar](20) NOT NULL, [KeyValue] [varchar](100) NOT NULL) ON [PRIMARY]

*/

--1、加多存款和储蓄进度

if exists(select name from sysobjects where name='pro_AddColumn' and type='p')
drop procedure pro_AddColumn
go
create procedure pro_AddColumn
@tableName varchar(100),@columNname varchar(100),@DbType varchar(32),@Msg varchar(50) output
as
declare @flag char(1)
set @flag=0
set @Msg='列增添失利!'
/*
向钦赐表增多列
*/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[葡京正网网投,'+@tablename+']') and objectProperty(id, N'IsUserTable') = 1)

if (NOT exists ( select * from dbo.syscolumns where name =@columnname and id in
(select id from dbo.sysobjects where id = object_id(N'[dbo].['+@tablename+']') and objectProperty(id, N'IsUserTable') = 1))
)
begin
  -- select 'not exist,now is adding...' 不存在,则添加
  declare @cmdAddColumn varchar(100)
  select  @cmdAddColumn='alter TABLE '+ @tablename+' ADD '+@columnname+' '+@DbType
  exec(@cmdAddColumn)
  set  @flag=1
  set @Msg='列增多立业成家!'
end
else
 begin
  -- select 'Existed !now is Deleteing......'
  -- exec pro_DropColumn @tablename,@columnname
  set  @flag=1
  set @Msg='列己存在!'
 end
select @flag
return @flag

--2、删除存储进度

if exists(select name from sysobjects where name='pro_DropColumn' and type='p')
drop procedure pro_DropColumn
go

create procedure pro_DropColumn
@tableName varchar(100),@columNname varchar(100),@Msg varchar(50) output
as
/*
删去钦赐表内定列
*/
begin
declare @flag char(1)

set @flag=0
set @Msg='列删除失利!'
-------------------------------删除进度开首------------------------------------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].['+@tablename+']') and objectProperty(id, N'IsUserTable') = 1)

if (exists ( select * from dbo.syscolumns where name =@columnname and id in
(select id from dbo.sysobjects where id = object_id(N'[dbo].['+@tablename+']') and objectProperty(id, N'IsUserTable') = 1))
)
begin  --如若存在那表和此列
--------------------------------删除节制开始-----------------------------------------------------
declare @tab varchar(100)
declare @defname varchar(100)
declare @cmd varchar(100)
declare @cmdDel varchar(100)
select @defname = name
FROM sysobjects so
JOIN sysconstraints sc
ON so.id = sc.constid
WHERE object_name(so.parent_obj) = @tablename
AND so.xtype = 'D'
AND sc.colid =
(SELECT colid FROM syscolumns
WHERE id = object_id(@tablename) AND
name = @columnname)

select @cmd='alter table '+ @tablename+ ' drop constraint '+ @defname
if @cmd is null print 'No default constraint to drop'   --删除己存在的限定
exec (@cmd)
-------------------------------删除约束停止-----------------------------------------------------------

-------------------------------删除内定列始发-----------------------------------------------------------
select @cmdDel='alter table '+@tablename+' drop column '+ @columnname
if @cmdDel is null
begin
  print '列删除战败'
  set @flag=0
  set @Msg='列删除战败!'
end
begin
 exec (@cmdDel)
 set @flag=1
 set @Msg='列删除成功!'
end
-------------------------------删除钦命列结束------------------------------------------------------
end
-------------------------------删除进度甘休----------------------------------------------------
else --不设有个别列,不用删除
 begin
   set @flag=1
   set @Msg='内定列海市蜃楼!'
 end


select @flag
return @flag
end
GO

--3、校勘存款和储蓄进程

if exists(select name from sysobjects where name='pro_AlterColumn' and type='p')
drop procedure pro_AlterColumn
go
create procedure pro_AlterColumn
@tableName varchar(100),@columNname varchar(100),@DbType varchar(32),@Msg varchar(50) output
as
declare @flag char(1)
set @flag=0
set @Msg='列增加退步!'
/*
  查找表中的列
*/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].['+@tablename+']') and objectProperty(id, N'IsUserTable') = 1)

if ( exists ( select * from dbo.syscolumns where name =@columnname and id in
(select id from dbo.sysobjects where id = object_id(N'[dbo].['+@tablename+']') and objectProperty(id, N'IsUserTable') = 1))
)
begin
  -- select 'Existsed ,now is alterring...' 修改
  declare @cmdAddColumn varchar(100)
  select  @cmdAddColumn='alter TABLE '+ @tablename+' alter column '+@columnname+' '+@DbType
  exec(@cmdAddColumn)
  set  @flag=1
  set @Msg='修正成功!'
end
else
 begin
  -- select 'not existed !' 不设有要改良的列
  set  @flag=1
  set @Msg='列己海市蜃楼!'
 end
select @flag
return @flag

 

GO

if exists (select * from dbo.sysobjects where id = object_id(N'PCreateGuid'卡塔尔(قطر‎ and OBJECTPROPERTY(id, N'IsProcedure'卡塔尔(قطر‎ = 1卡塔尔drop procedure PCreateGuidGO--------------------------------------用项:扩大一条记录 个人消息--项目名称:--表达:--时间:二零一三-7-4 15:34:12,2013-08-15------------------------------------CREATE PROCEDURE PCreateGuid@tableName varchar(50卡塔尔,@keyname varchar(100卡塔尔,@guid varchar(100State of Qatar outasif not exists(select * from TbGuidTable where tablename=@tableName and keyname=@keyname)insert into TbGuidTable (tablename,keyname,keyvalue) values(@tableName,@keyname,10000)update TbGuidTable set keyvalue=keyvalue+1 where tablename=@tableName and keyname=@keynameselect @guid=cast(keyvalue as varchar) from TbGuidTable where tablename=@tableName and keyname=@keynameGo

---*****---declare @tableName varchar(50)declare @keyname varchar(100)declare @guid varchar(100)set @tableName='tb1'set @keyname='def'execute PCreateGuid @tableName,@keyname,@guid outprint @guid

本文由葡京网投哪个正规发布于新葡亰-数据,转载请注明出处:mssqlserver中最大ID冲突解决办法,通过存储过程动态操作表中的列

关键词:

上一篇:中的通配符,有些正则表达式可以通过like实现

下一篇:动态生成cursor或者动态执行SQL的DML操作,SQL开发中动态SQL的使用方法