1 Add Field
Copy Code code as follows:
IF not EXISTS (SELECT * from syscolumns WHERE id=object_id (' Querydatasource ') and name = ' iscrosstable ')
BEGIN
ALTER TABLE querydatasource ADD iscrosstable bit default (0)--insert Field
End
2 Stored Procedures
Copy Code code as follows:
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 Creating a Table
Copy Code code as follows:
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] (a) NULL,
[Plsystemflag] [Char] (2) NULL,
CONSTRAINT [Xpkplsystem] PRIMARY KEY CLUSTERED
(
[Plsystemid] Asc
)
) on [PRIMARY]
End
Go
4 Cursors
Copy Code code as follows:
Begin
DECLARE @PluserID varchar (8)
Declare plusercurse Cursor for select Pluserid from Pluser where pcanceljudge=0
Open Plusercurse--opening cursors
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 isn't 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 Implementing a For loop with a while
Copy Code code as follows:
Create Proc Insertsql
As
Begin
DECLARE @Count int
Set @Count = 0;
while (@Count <200000)
Begin
Print (@Count);
Insert into Student values (' Young ', ' M ',, ' fujianxiament ');
Set @Count = @Count +1;
End
End
