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
