登录
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
@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
@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
(
@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