注册及添加

 

 

CREATE PROCEDURE Pr_AddUser
(
     @UserName  varchar(100),
     @UserPassword  varchar(200),
     @UserEmail  varchar(200),
     @UserCategory  varchar(200),
     @IsUniqueEmail  int  OUTPUT,
     @UserID  int  OUTPUT
   
)

AS

    SET NOCOUNT ON

    DECLARE @ValidEmail nvarchar(1024),
                      @CurrentID int
   
    IF ( @UserCategory = 1 )

    BEGIN
            --检查用户是否已经存在
            SELECT @CurrentID=Rc_ID FROM  Rc_Info   WHERE ( Rc_Uname =@UserName )
            --如果用户不存在，再校验用户注册的 email 是否重复，如果不重得，则用户可以注册，把注册信息插入数据库表  Rc_info 中
            IF @currentID IS NULL 
                                  BEGIN 
                                                                           SELECT @ValidEmail=Rc_Email FROM  Rc_Info   WHERE (Rc_Email = @UserEmail )
                                                                                          IF @ValidEmail IS NULL
                                                                                                             BEGIN
                                      INSERT INTO   Rc_Info 
                                                  (
                                                       Rc_Uname,
                                                       Rc_Upwd,
                                                                                                                                  Rc_Email     
                                                  )
                                                 VALUES
                                                  (
                                                         @UserName,
                                                         @UserPassword ,
                                                                                                                                    @UserEmail 
                                                  )

                                                                                                                           SET  @IsUniqueEmail  = 1
                                                                                                                           SET  @UserID =  @@Identity
                                                               END 

                                                                                             ELSE
                                                                                                             BEGIN
                                                                                                                           SET  @IsUniqueEmail  = -1                                 
                                                                                                                           
                                                               END 

            
                     
                                  End 
            --如果用户已经存在
            ELSE
                                 BEGIN
                                 SET  @UserID =  -1
                                 END 

    END

    ELSE IF ( @UserCategory = 2 )

             BEGIN
            --检查企业的用户是否已经存在
            SELECT @CurrentID=Company_ID FROM  Company_Info   WHERE ( Company_Uname =@UserName )
            --如果用户不存在，再校验用户注册的 email 是否重复，如果不重得，则用户可以注册，把注册信息插入数据库表  Company_info 中
            IF @currentID IS NULL 
                                  BEGIN 
                                                                           SELECT @ValidEmail=Company_Email FROM  Company_Info   WHERE (Company_Email  = @UserEmail )
                                                                                          IF @ValidEmail IS NULL
                                                                                                             BEGIN
                                      INSERT INTO  Company_Info
                                                  (
                                                       Company_Uname,
                                                       Company_Upwd,
                                                                                                                                  Company_Email  
                                                  )
                                                 VALUES
                                                  (
                                                         @UserName,
                                                         @UserPassword ,
                                                                                                                                    @UserEmail 
                                                  )

                                                                                                                           SET @IsUniqueEmail  = 1
                                                                                                                           SET  @UserID =  @@Identity
                                                               END 

                                                                                             ELSE
                                                                                                             BEGIN
                                                                                                                           SET @IsUniqueEmail  = -1                                 
                                                                                                                          
                                                               END 

            
                     
                                  End 
            --如果用户已经存在
            ELSE
                                 BEGIN
                                 SET  @UserID =  -1
                                 END 

    END

    ELSE  IF ( @UserCategory = 3 )

            BEGIN
            --检查培训机构用户是否已经存在
            SELECT @CurrentID=TrainSchoolID FROM  TrainSchool   WHERE ( TrainUserName =@UserName )
            --如果用户不存在，再校验用户注册的 email 是否重复，如果不重得，则用户可以注册，把注册信息插入数据库表  TrainSchool 中
            IF @currentID IS NULL 
                                  BEGIN 
                                                                           SELECT @ValidEmail =TrainEmail FROM  TrainSchool    WHERE (TrainEmail  = @UserEmail )
                                                                                          IF @ValidEmail IS NULL
                                                                                                             BEGIN
                                      INSERT INTO   TrainSchool
                                                  (
                                                       TrainUserName,
                                                       TrainUserPwd,
                                                                                                                                  TrainEmail    
                                                  )
                                                 VALUES
                                                  (
                                                         @UserName,
                                                         @UserPassword ,
                                                                                                                                    @UserEmail 
                                                  )

                                                                                                                           SET @IsUniqueEmail  = 1
                                                                                                                           SET  @UserID =  @@Identity
                                                               END 

                                                                                             ELSE
                                                                                                             BEGIN
                                                                                                                           SET @IsUniqueEmail  = -1                                 
                                                                                                                         
                                                               END 

            
                     
                                  End 
            --如果用户已经存在
            ELSE
                                 BEGIN
                                 SET  @UserID =  -1
                                 END 

    END
  

RETURN
GO

 

 

CREATE PROCEDURE  Pr_AddOAMessageData
(
     @MessageTitle NVarChar (100),
     @MessageContent Ntext,
     @MessageLevel NVarChar (50),
     @MessageAuthorID NVarChar (50),
     @MessageAuthor NVarChar (50),
     @MessageAuthorIP NVarChar (100),
     @MessageAuthorDepartCode NVarChar (50),
     @MessageAuthorDepartLevel NVarChar (50),
     @MessageSendToDepartCode NVarChar (4000),
     @ID  int OUTPUT       
                                
)

AS


INSERT INTO MessageData
 (
MessageTitle,
MessageContent,
MessageLevel,
MessageAuthorID,
MessageAuthor,
MessageAuthorIP,
MessageAuthorDepartCode,
MessageAuthorDepartLevel,
MessageSendToDepartCode
)
VALUES
 (
@MessageTitle,
@MessageContent,
@MessageLevel,
@MessageAuthorID,
@MessageAuthor,
@MessageAuthorIP,
@MessageAuthorDepartCode,
@MessageAuthorDepartLevel,
@MessageSendToDepartCode
)
 
SET  @ID =  @@Identity


RETURN @ID
GO