sql.txt

是 个典型的 OA系统

SELECT dbo.Files.[Date], dbo.Files.State, dbo.Files.FileKind, dbo.Files.Title, 
      dbo.Files.Writer, dbo.Organization.OrganizationName, dbo.System.SystemName, 
      dbo.Persons.Name, dbo.Files.Contents, dbo.Files.ContentType, dbo.Files.ByteSize 
FROM dbo.Files INNER JOIN
      dbo.Organization ON 
      dbo.Files.OrganizationID = dbo.Organization.OrganizationID INNER JOIN
      dbo.System ON dbo.Files.SystemID = dbo.System.SystemID INNER JOIN
      dbo.Persons ON dbo.Files.PersonID = dbo.Persons.PersonID
WHERE FileID=@FileID
RETURN
|
CREATE PROCEDURE [dbo].[FilePrompt] (@AccepterID nvarchar(120))  AS
SELECT * FROM  Files  WHERE AccepterID LIKE '%('+@AccepterID+')%'  AND state NOT LIKE '%('+@AccepterID+')%'
return
|
CREATE procedure [dbo].[GetInfomation] (@pagesize int,@pageindex int,@InfoCategoryID int,@docount bit) AS
	set nocount on
	if(@docount=1)
	select count(InfoID) from Infomation where InfoCategoryID = @InfoCategoryID
	else
	begin
		declare @indextable table(InfoID int identity(1,1),nid int)
		declare @PageLowerBound int
		declare @PageUpperBound int
		set @PageLowerBound=(@pageindex-1)*@pagesize
		set @PageUpperBound=@PageLowerBound+@pagesize
		set rowcount @PageUpperBound
		insert into @indextable(nid) select InfoID from Infomation order by Date desc
		select O.InfoID,O.Writer,O.title,O.Date from Infomation O,@indextable t where O.InfoID=t.nid
		and t.InfoID>@PageLowerBound and t.InfoID<=@PageUpperBound and O.InfoCategoryID=@InfoCategoryID order by t.InfoID
	        end
            set nocount off
|
CREATE PROCEDURE [dbo].[InfoCategorySelect] AS
SELECT dbo.InfoCategories.*, dbo.Organization.OrganizationName AS Expr1
FROM dbo.InfoCategories INNER JOIN
      dbo.Organization ON 
      dbo.InfoCategories.OrganizationID = dbo.Organization.OrganizationID
return
|
CREATE Procedure [dbo].[InfomationContents] (@InfoID int) AS
    SELECT dbo.Infomation.InfoID, dbo.Infomation.Writer, dbo.Infomation.Title, 
      dbo.Infomation.Contents, dbo.Infomation.[Date], dbo.Infomation.State, 
      dbo.Infomation.InfoCategoryID, dbo.Persons.Name
FROM dbo.Infomation INNER JOIN
      dbo.Persons ON dbo.Infomation.PersonID = dbo.Persons.PersonID WHERE InfoID=@InfoID
    return
|
CREATE PROCEDURE [dbo].[InsertDocument] (@Title nvarchar(100),@Contents image,@PersonID int,@AccepterID nvarchar(500),@State char(10),@Process nvarchar(500),@WriterID nvarchar(50)) AS
Insert into Document(Title,Contents,PersonID,AccepterID,Date,State,Process,WriterID) values (@Title,@Contents,@PersonID,@AccepterID,GETDATE(),@State,@Process,@WriterID)
return
|
CREATE PROCEDURE [dbo].[InsertFile] (@Title nvarchar(50),@Contents image,@PersonID int,@AccepterID nvarchar(500),@State int,@ContentType nvarchar(50),@ByteSize nvarchar(50),@SystemID int,@OrganizationID int,@FileKind nvarchar(50)) AS
Insert into Files(Title,Contents,PersonID,AccepterID,Date,State,ContentType,ByteSize,SystemID,OrganizationID,FileKind) values (@Title,@Contents,@PersonID,@AccepterID,GETDATE(),@State,@ContentType,@ByteSize,@SystemID,@OrganizationID,@FileKind)
return
|
CREATE PROCEDURE [dbo].[InsertInfo] (@Title nvarchar(80),@Contents image,@PersonID int,@Writer nvarchar(50),@InfoCategoryID int) AS
Insert into Infomation(Title,Contents,PersonID,InfoCategoryID,Date,State,Writer) values (@Title,@Contents,@PersonID,@InfoCategoryID,GETDATE(),0,@Writer)
return
|
CREATE PROCEDURE [dbo].[InsertInfoCategory](@InfoCategoryName nvarchar(50),@OrganizationID int) AS
Insert into InfoCategories(InfoCategoryName,OrganizationID) values (@InfoCategoryName,@OrganizationID)
return
|
CREATE PROCEDURE [dbo].[InsertJob] (@JobName nvarchar(50)) AS
Insert into Job(JobName) values (@JobName)
return
|
CREATE PROCEDURE [dbo].[InsertMeeting] (@Title nvarchar(100),@Caller nvarchar(50), @AccepterID nvarchar(500),@Address nvarchar(50),@Attends nvarchar(500),@State  nvarchar(120),@MeetingDate nvarchar(50),@PersonID int) AS
Insert into Meeting(Title,Caller,AccepterID,Date,State,MeetingDate,Address,Attends,PersonID,Result) values (@Title,@Caller,@AccepterID,GETDATE(),@State,@MeetingDate,@Address,@Attends,@PersonID,0)
return
|
CREATE PROCEDURE [dbo].[InsertMessage] (@Contents nvarchar(1000),@PersonID int,@AccepterID nvarchar(500),@State int,@Attachment nvarchar(50)) AS
Insert into Message(Contents,PersonID,AccepterID,Date,State,Attachment) values (@Contents,@PersonID,@AccepterID,GETDATE(),@State,@Attachment)
return
|
CREATE PROCEDURE [dbo].[InsertNotification] (@Contents nvarchar(200),@PersonID int) AS
Insert into Notification(Contents,PersonID,Date,State) values (@Contents,@PersonID,GETDATE(),0)
return
|
CREATE PROCEDURE [dbo].[InsertOnlineUser] (@UserName nvarchar(50),@UserSN nvarchar(50)) AS
Insert into Online(UserName,UserSN,Date) values (@UserName,@UserSN,GETDATE())
return
|
CREATE PROCEDURE [dbo].[InsertOrganization] (@OrganizationName nvarchar(50),@SystemID nvarchar(50)) AS
Insert into Organization(OrganizationName,SystemID) values (@OrganizationName,@SystemID)
return
|
CREATE PROCEDURE [dbo].[InsertPerson] (@Name nvarchar(100),@OrganizationID nvarchar(50), @JobID nvarchar(50),@Password nvarchar(50)) AS
Insert into Persons(Name,OrganizationID,JobID,Password) values (@Name,@OrganizationID,@JobID,@Password)
return
|
CREATE PROCEDURE [dbo].[InsertSchedule] (@Contents nvarchar(200),@PlanTime Datetime,@State int,@PersonID int) AS
Insert into Schedule(Contents,PlanTime,Date,State,PersonID,Result) values (@Contents,@PlanTime,GETDATE(),@State,@PersonID,0)
return
|
CREATE PROCEDURE [dbo].[InsertSystem] (@SystemName nvarchar(50)) AS
Insert into System(SystemName) values (@SystemName)
return
|
CREATE Procedure [dbo].[JobName] (@JobID int) AS
    SELECT * FROM Job  WHERE JobID=@JobID
    return
|
CREATE Procedure [dbo].[JobSelect]   AS
    SELECT * FROM Job ORDER BY JobID ASC
    return
|
CREATE Procedure [dbo].[ManageJob]   AS
    SELECT * FROM Job WHERE JobName NOT LIKE '系统管理员' AND JobName  NOT LIKE '文件管理员' ORDER BY JobID ASC
    return
|
CREATE Procedure [dbo].[MeetingContents] (@MeetingID int) AS
    SELECT * FROM Meeting WHERE MeetingID=@MeetingID
    return
|
CREATE PROCEDURE [dbo].[MeetingPrompt] (@AccepterID nvarchar(120))  AS
SELECT * FROM Meeting WHERE State NOT LIKE  '%('+@AccepterID+')%'  AND AccepterID LIKE  '%('+@AccepterID+')%'
|
CREATE Procedure [dbo].[MessageContents] (@MessageID int) AS
    SELECT * FROM Message WHERE MessageID=@MessageID
    return
|
CREATE PROCEDURE [dbo].[MessagePrompt] (@AccepterID nvarchar(120),@State int = null)  AS
IF  isnull(@State,0) = 0
Begin 
SELECT * FROM Message WHERE AccepterID = @AccepterID AND State =0
End
ELSE
BEGIN
SELECT * FROM Message WHERE AccepterID = @AccepterID AND State = @State
END
return
|
CREATE PROCEDURE [dbo].[NotificationPrompt]  AS
SELECT * FROM Notification WHERE  State = 0
return
|
CREATE Procedure [dbo].[OnlineUser]  AS
    SELECT * FROM Online ORDER BY Date DESC
    return
|
CREATE Procedure [dbo].[OrganizationName] (@OrganizationID INT)  AS
    SELECT * FROM Organization  WHERE  OrganizationID = @OrganizationID
    return
|
CREATE Procedure [dbo].[OrganizationSelect]   AS
    SELECT * FROM Organization ORDER BY OrganizationID ASC
    return
|
CREATE Procedure [dbo].[PersonID] (@Name nvarchar(50)) AS
    SELECT * FROM Persons WHERE Name=@Name
    return
|
CREATE Procedure [dbo].[PersonID0] (@Name nvarchar(50),@Password nvarchar(50)) AS
    SELECT * FROM Persons WHERE Name=@Name AND Password=@Password
    return
|
CREATE Procedure [dbo].[PersonIDTree] (@Name nvarchar(50),@JobID nvarchar(50)) AS
    SELECT * FROM Persons WHERE Name<>@Name AND JobID LIKE '%'+@JobID+'%' ORDER BY PersonID ASC
    return
|
CREATE Procedure [dbo].[PersonIDTree0] (@JobID nvarchar(50)) AS
    SELECT * FROM Persons WHERE  JobID LIKE '%'+@JobID+'%' ORDER BY PersonID ASC
    return
|
CREATE Procedure [dbo].[PersonName] (@PersonID int) AS
    SELECT * FROM Persons WHERE PersonID=@PersonID
    return
|
CREATE PROCEDURE [dbo].[PromptSchedule] (@PersonID INT)  AS
SELECT * FROM Schedule WHERE DATEPART(dayofyear,PlanTime) =  DATEPART(dayofyear, getdate()) AND PlanTime>getdate() AND Result=0 AND PersonID=@PersonID
RETURN
|
CREATE PROCEDURE [dbo].[PromptSchedule1] (@PersonID INT)  AS
SELECT * FROM Schedule WHERE  PlanTime<getdate() AND Result=0   AND PersonID=@PersonID
RETURN
|
CREATE PROCEDURE [dbo].[SelectALLInfomation] AS
SELECT dbo.InfoCategories.InfoCategoryName, dbo.Persons.Name, dbo.Infomation.InfoID, 
      dbo.Infomation.Writer, dbo.Infomation.Title,  
      dbo.Infomation.[Date], dbo.Infomation.State
FROM dbo.Infomation INNER JOIN
      dbo.Persons ON dbo.Infomation.PersonID = dbo.Persons.PersonID INNER JOIN
      dbo.InfoCategories ON 
      dbo.Infomation.InfoCategoryID = dbo.InfoCategories.InfoCategoryID  ORDER BY dbo.Infomation.InfoID DESC
return
|
CREATE PROCEDURE [dbo].[SelectAllDocument]  AS
SELECT * FROM Document
return
|
CREATE PROCEDURE [dbo].[SelectAllFile]  AS
SELECT * FROM Files ORDER BY FileID
return
|
CREATE PROCEDURE [dbo].[SelectAllMeeting]  AS
SELECT * FROM Meeting ORDER BY MeetingID
return
|
CREATE PROCEDURE [dbo].[SelectAllMessage]  AS
SELECT * FROM Message ORDER BY MessageID
return
|
CREATE PROCEDURE [dbo].[SelectAllNotification]  AS
SELECT * FROM Notification ORDER BY NotificationID DESC
return
|
CREATE PROCEDURE [dbo].[SelectAllSchedule]  AS
SELECT * FROM Schedule
return
|
CREATE Procedure [dbo].[SelectFileManager]  AS
    SELECT * FROM Job WHERE JobName='文件管理员'
    return
|
CREATE Procedure [dbo].[SelectInfoCategories]   AS
    SELECT * FROM InfoCategories ORDER BY InfoCategoryID ASC
    return
|
CREATE Procedure [dbo].[SelectInfoCategoryName] (@InfoCategoryID int) AS
    SELECT InfoCategoryName FROM InfoCategories WHERE  InfoCategoryID=@InfoCategoryID
    return
|
CREATE PROCEDURE [dbo].[SelectInfoCount]  AS
select Count(*) from Infomation
return
|
CREATE Procedure [dbo].[SelectMessageAttachment] (@MessageID int) AS
    SELECT Attachment FROM Message WHERE MessageID=@MessageID
    return
|
CREATE PROCEDURE [dbo].[SelectMyChechedDocument]  (@CheckedMarker NVARCHAR(120))  AS
SELECT * FROM Document WHERE CheckedMarker LIKE '%('+@CheckedMarker+')%'
return
|
CREATE PROCEDURE [dbo].[SelectMyDocument]  (@WriterID NVARCHAR(50))  AS
SELECT * FROM Document WHERE WriterID =  @WriterID
return
|
CREATE PROCEDURE [dbo].[SelectMySchedule] (@PersonID int)  AS
SELECT * FROM Schedule WHERE PersonID = @PersonID
return
|
CREATE Procedure [dbo].[SelectNotification](@NotificationID int)  AS
    SELECT * FROM Notification WHERE NotificationID = @NotificationID
    return
|
CREATE Procedure [dbo].[SelectOrganization] (@SystemID nvarchar(50),@TypeID int) AS
    SELECT * FROM Organization WHERE SystemID LIKE @SystemID  AND TypeID=@TypeID ORDER BY OrganizationID ASC
    return
|
CREATE Procedure [dbo].[SelectOrganization0] (@SystemID nvarchar(50)) AS
    SELECT * FROM Organization WHERE SystemID LIKE  '%'+@SystemID+ '%'  ORDER BY OrganizationID ASC
    return
|
CREATE Procedure [dbo].[SelectOrganizationName] (@OrganizationID int) AS
    SELECT * FROM Organization WHERE  OrganizationID=@OrganizationID
    return
|
CREATE PROCEDURE [dbo].[SelectPendingDocument]  (@AccepterID NVARCHAR(120))  AS
SELECT * FROM Document WHERE AccepterID=@AccepterID
return
|
CREATE PROCEDURE [dbo].[SelectReceiveFile] (@AccepterID NVARCHAR(120))  AS
SELECT * FROM Files WHERE AccepterID LIKE '%('+@AccepterID+')%'
return
|
CREATE PROCEDURE [dbo].[SelectReceiveMeeting]  (@AccepterID NVARCHAR(120))  AS
SELECT * FROM Meeting WHERE AccepterID LIKE '%('+@AccepterID+')%'
return
|
CREATE PROCEDURE [dbo].[SelectReceiveMessage]  (@AccepterID NVARCHAR(120))  AS
SELECT * FROM Message WHERE AccepterID=@AccepterID
return
|
CREATE PROCEDURE [dbo].[SelectSenderMessage]  (@PersonID int)  AS
SELECT * FROM Message WHERE PersonID = @PersonID AND AccepterID LIKE '(%'
return
|
CREATE Procedure [dbo].[SelectSystemManager]  AS
    SELECT * FROM Job WHERE JobName='系统管理员'
    return
|
CREATE Procedure [dbo].[SelectSystemName] (@SystemID int) AS
    SELECT * FROM System WHERE  SystemID=@SystemID
    return
|
CREATE PROCEDURE [dbo].[SelectTopInfo] (@InfoCategoryID int) AS
select  Top 6 * from Infomation where InfoCategoryID = @InfoCategoryID ORDER BY Date DESC
return
|
CREATE Procedure [dbo].[SystemSelect] AS
    SELECT * FROM System ORDER BY SystemID ASC
    return
|
CREATE PROCEDURE [dbo].[UpdateDocument](@DocumentID int,@State char(10),@Result nvarchar(4000),@AccepterID nvarchar(500),@PersonID int,@CheckedMarker nvarchar(120)) AS
UPDATE Document SET State = @State, Result=@Result,AccepterID=@AccepterID,PersonID=@PersonID,CheckedMarker=@CheckedMarker where DocumentID = @DocumentID
return
|
CREATE PROCEDURE [dbo].[UpdateDocumentState](@DocumentID int,@State char(10),@Result nvarchar(4000),@AccepterID nvarchar(500)) AS
UPDATE Document SET State = @State, Result=@Result,AccepterID=@AccepterID where DocumentID = @DocumentID
return
|
CREATE PROCEDURE [dbo].[UpdateFileState](@FileID int,@State nvarchar(120)) AS
UPDATE Files SET State = @State where FileID = @FileID
return
|
CREATE PROCEDURE [dbo].[UpdateInfoCategory](@InfoCategoryName  NVARCHAR(50),@InfoCategoryID  int,@OrganizationID int) AS
UPDATE InfoCategories SET  InfoCategoryName= @InfoCategoryName,OrganizationID = @OrganizationID where InfoCategoryID = @InfoCategoryID
return
|
CREATE PROCEDURE [dbo].[UpdateInfomation](@InfoID int,@PersonID int,@Title nvarchar(80),@Contents image,@Writer nvarchar(50),@InfoCategoryID int) AS
UPDATE Infomation SET PersonID=@PersonID, Title=@Title, Contents=@Contents,Writer=@Writer,InfoCategoryID=@InfoCategoryID,Date=GETDATE() where InfoID = @InfoID
return
|
CREATE PROCEDURE [dbo].[UpdateInfomationState](@InfoID int,@State int) AS
UPDATE Infomation SET State = @State
  where InfoID = @InfoID
return
|
CREATE PROCEDURE [dbo].[UpdateJob](@JobName  NVARCHAR(50),@JobID  int) AS
UPDATE Job SET  JobName= @JobName where JobID = @JobID
return
|
CREATE PROCEDURE [dbo].[UpdateMeetingResult](@MeetingID int,@Result nvarchar(10)) AS
UPDATE Meeting SET Result = @Result 
where MeetingID = @MeetingID
return
|
CREATE PROCEDURE [dbo].[UpdateMeetingState](@MeetingID int,@State nvarchar(500)) AS
UPDATE Meeting SET State = @State ,ReadTime=GETDATE()
where MeetingID = @MeetingID
return
|
CREATE PROCEDURE [dbo].[UpdateMessageState](@MessageID int,@State int) AS
UPDATE Message SET State = @State,ReadTime=GETDATE()
  where MessageID = @MessageID
return
|
CREATE PROCEDURE [dbo].[UpdateNotificationState](@NotificationID int) AS
update Notification set State = 1 where NotificationID = @NotificationID
return
|
CREATE PROCEDURE [dbo].[UpdateOnlineUser](@UserSN NVARCHAR(50),@UserName NVARCHAR(50)) AS
UPDATE Online SET UserSN = @UserSN, Date =GETDATE() where UserName=@UserName
return
|
CREATE PROCEDURE [dbo].[UpdateOrganization](@OrganizationID int,@OrganizationName  NVARCHAR(50),@SystemID  NVARCHAR(50)) AS
UPDATE Organization SET  OrganizationName= @OrganizationName,SystemID = @SystemID where OrganizationID=@OrganizationID
return
|
CREATE PROCEDURE [dbo].[UpdatePerson](@PersonID int,@Name  NVARCHAR(50),@OrganizationID  NVARCHAR(50),@JobID  NVARCHAR(50)) AS
UPDATE Persons SET  Name= @Name,OrganizationID = @OrganizationID, JobID = @JobID where PersonID=@PersonID
return
|
CREATE PROCEDURE [dbo].[UpdatePersonFriend](@PersonID int,@Friends  NVARCHAR(200)) AS
UPDATE Persons SET  Friends= @Friends where PersonID=@PersonID
return
|
CREATE PROCEDURE [dbo].[UpdatePersonPass](@PersonID int,@Password  NVARCHAR(50)) AS
UPDATE Persons SET  Password=@Password ,State = '0' where PersonID=@PersonID
return
|
CREATE PROCEDURE [dbo].[UpdatePersonRegisterInfo](@PersonID int,@Password  NVARCHAR(50),@Phoneo  NVARCHAR(50),@Phonef  NVARCHAR(50),@Mobile  NVARCHAR(50),@Address  NVARCHAR(50)) AS
UPDATE Persons SET  Password=@Password ,Phoneo = @Phoneo,Phonef = @Phonef,Mobile = @Mobile,Address = @Address where PersonID=@PersonID
return
|
CREATE PROCEDURE [dbo].[UpdateScheduleResult](@ScheduleID int,@Result nvarchar(10)) AS
UPDATE Schedule SET Result = @Result 
where ScheduleID = @ScheduleID
return
|
CREATE PROCEDURE [dbo].[UpdateSystem](@SystemName  NVARCHAR(50),@SystemID  int) AS
UPDATE System SET  SystemName= @SystemName where SystemID = @SystemID
return
|
CREATE PROCEDURE [dbo].[ViewMessageState] (@Contents nvarchar(1000),@PersonID INT,@AccepterID NVARCHAR(500)) AS
SELECT State,ReadTime FROM Message WHERE Contents=@Contents AND PersonID= @PersonID AND AccepterID= @AccepterID
return
|

Insert into Job(JobName) values ('系统管理员');
|
Insert into Job(JobName) values ('文件管理员');
|
Insert into Persons(Name,JobID,Password) values ('Admin',1,'7DDCC5C9FA4B0CEDFA1E385FEDB908EE');