------------------------------------------------------------------------------
--登陆存储过程
------------------------------------------------------------------------------
------@IsLogin :-1:密码错误 0: 用户名不存在;1:登陆成功 2:用户冻结
Use NewsData
Go
if exists(select * from sysobjects where [name]='xp_Login' and [type]='P')
begin
drop proc xp_Login
end
Go
create proc xp_Login
@UserID varchar(20),
@Password varchar(20),
@UserState int output,
@isLogin int output,
@LastLoginTime datetime output,
@LastLoginIP varchar(20) output,
@UserMarker int output
as
begin
set @isLogin=(select count(0) from TB_Users where userID = @UserID and password=@Password and UserState=1);
if(@isLogin='1')
begin
begin
declare @thisLoginIP varchar(20);
set @thisLoginIP=@LastLoginIP;
set @LastLoginTime =(select LastLoginTime from TB_Users where UserID=@UserID);
set @LastLoginIP =(select LastLoginIP from TB_Users where UserID=@UserID);
set @UserMarker = (select UserMarker from TB_Users where UserID=@UserID);
update TB_Users set LastLoginTime =getdate(),lastLoginIP = @ThisLoginIP,@UserMarker=@UserMarker+2 where UserID = @UserID;
select roleID from TB_Users_Roles where UserID=@UserID;
end
end
else
begin
declare @checkState int;
set @checkState=(select count(0) from TB_Users where userID =@UserID and UserState=1);
if(@checkState=1)
begin
declare @checkPwd int;
set @checkPwd=100;
set @checkPwd=(select UserState from TB_Users where userID = @UserID);
if(@checkPwd=1)
begin
set @isLogin=-1;
end
else
begin
set @isLogin=0;
end
end
else
begin
declare @checkName int;
set @checkName=(select count(0) from TB_Users where userID =@UserID)
if(@checkName=1)
begin
set @isLogin=2;
end
else
begin
set @isLogin=0;
end
end
end
end
GO
declare
@UserState int,
@isLogin int,
@LastLoginTime datetime,
@LastLoginIP varchar(20),
@UserMarker int
set @LastLoginIP='192.168.2.116'
set @LastLoginTime='2009-1-18'
exec xp_Login 'admin','891221',@UserState output,@isLogin output,@LastLoginTime output,@LastLoginIP output,@UserMarker output
print @isLogin
print @LastLoginTime
print @LastLoginIP
SQL登录存储过程代码
最新推荐文章于 2020-09-16 20:10:30 发布