1 增加字段 复制代码 代码如下: IF NOT EXISTS (SELECT * FROM SYSCOLUMNS WHERE ID=OBJECT_ID('QueryDataSource') and name = 'IsCrossTable') BEGIN ALTER TABLE QueryDataSource ADD IsCrossTable bit default(0) --插入字段 END 2 存储过
1 增加字段 复制代码 代码如下: IF NOT EXISTS (SELECT * FROM SYSCOLUMNS WHERE ID=OBJECT_ID('QueryDataSource') and name = 'IsCrossTable') BEGIN ALTER TABLE QueryDataSource ADD IsCrossTable bit default(0) --插入字段 END 2 存储过程 复制代码 代码如下: if exists (select * from dbo.sysobjects where id = object_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(2) ) AS UPDATE dbo.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 ) OR PLDepartment.DepartmentID=(select DepartmentID from PLUser where PLUserID =@PluserID) ) 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(@Count<200000) Begin Print(@Count); Insert into Student values('YOUNG','M',100,'FUJIANXIAMENT'); set @Count=@Count+1; END END
