SQL规范化的数据修改语句

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