Pr_Userlogin登录，HtmlEditor_Update事务更新，Pr_CheckUserName检测用户名可否注册，存储过程

 

登录

CREATE PROCEDURE   Pr_Userlogin

     @UserCategory nvarchar(50),
     @UserName nvarchar(100),
     @UserPwd nvarchar(100),
     @UserNameBool nvarchar(50) OUTPUT,
     @UserPwdBool nvarchar(50) OUTPUT,
     @UserID int OUTPUT,
     @LoginSuccess int OUTPUT

 AS

     IF (@UserCategory = 1) 

          Begin
       
                    SELECT Rc_Uname  FROM  Rc_Info WHERE  Rc_Uname = @UserName
                             IF @@ROWCOUNT = 0
                                    BEGIN
                                                  SET  @UserNameBool = -1
                                    END
                            ELSE
                                   BEGIN
                                                     
                                                   SELECT  @UserID = Rc_ID   FROM  Rc_Info WHERE  Rc_Uname = @UserName and  Rc_Upwd =  @UserPwd
                                                                  IF @@ROWCOUNT = 0
                                                                            BEGIN
                                                                                    SET @UserPwdBool = -1
                                                                            END

                                                                  ELSE
                                                                             BEGIN
                                                                              
                                                                                   SET  @LoginSuccess = 1
                                                                                   
                                                                             END
                                                                        
                                   END
          End

     IF (@UserCategory = 2) 

          Begin
       
                    SELECT Company_Uname  FROM  Company_Info WHERE   Company_Uname = @UserName
                             IF @@ROWCOUNT = 0
                                    BEGIN
                                                  SET  @UserNameBool = -1
                                    END
                            ELSE
                                   BEGIN
                                                   SELECT  @UserID = Company_ID  FROM   Company_Info WHERE  Company_Uname = @UserName AND Company_Upwd=   @UserPwd
                                                                  IF @@ROWCOUNT = 0
                                                                            BEGIN
                                                                                    SET @UserPwdBool = -1
                                                                            END

                                                                  ELSE
                                                                             BEGIN
                                                                                   SET  @LoginSuccess = 1
                                                                             END
                                                                        
                                   END
          End

     IF (@UserCategory = 3) 

         Begin
       
                    SELECT TrainUserName  FROM  TrainSchool  WHERE  TrainUserName = @UserName
                             IF @@ROWCOUNT = 0
                                    BEGIN
                                                  SET  @UserNameBool = -1
                                    END
                            ELSE
                                   BEGIN
                                                   SELECT @UserID =  TrainSchoolID  FROM TrainSchool WHERE  TrainUserName = @UserName AND TrainUserPwd =   @UserPwd
                                                                  IF @@ROWCOUNT = 0
                                                                            BEGIN
                                                                                    SET @UserPwdBool = -1
                                                                            END

                                                                  ELSE
                                                                             BEGIN
                                                                                   SET  @LoginSuccess = 1
                                                                             END
                                                                        
                                   END
          End
GO

 

事务更新

 

CREATE PROCEDURE HtmlEditor_Update

@ID int,
@GetTitle nvarchar(50),
@GetCategory nvarchar(50),
@GetIfImage nvarchar(50),
@GetContent text

as      

BEGIN TRANSACTION

UPDATE ColumnNews  SET InfoTitle  = @GetTitle , InfoCategory = @GetCategory , IfImage = @GetIfImage, InfoContent = @GetContent  WHERE Info_ID = @ID


IF @@ERROR = 0
BEGIN
    COMMIT TRANSACTION
END
ELSE
BEGIN
   ROLLBACK TRANSACTION
   RAISERROR('Audit of rcinfo creation failed.',1,1)
   INSERT INTO tblErrorLog(ErrType,Msg)
      VALUES('Audit Failure','Audit of rcinfo creation failed.')
       IF @@ERROR <>0
       BEGIN
           RAISERROR('Error log could''t be updated.',1,1)
       END
 END
GO

 

CREATE PROCEDURE Pr_CheckUserName
(
     @UserName varchar(100),
     @UserCategory varchar(100),
     @UserID int OUTPUT
    
)
AS
    SET NOCOUNT ON
    DECLARE @currentID int
    --检查用户是否已经存在
    
   IF(@UserCategory = 1)
             BEGIN
             SELECT @currentID=Rc_ID FROM  Rc_Info    WHERE (Rc_Uname =@UserName )
             END
   IF (@UserCategory = 2)
             BEGIN
             SELECT @currentID=Company_ID FROM  Company_Info    WHERE (Company_Uname =@UserName )
             END
  IF (@UserCategory = 3)
             BEGIN
             SELECT @currentID=TrainSchoolID FROM  TrainSchool    WHERE (TrainUserName =@UserName )
             END

   --如果用户不存在，返回1
    IF @currentID IS NULL 
    BEGIN 
        SET  @UserID =  1
    End 
    --如果用户已经存在
   ELSE
   BEGIN
   SET  @UserID =  -1
   END 
RETURN
GO