SQL登入預存程序代碼

------------------------------------------------------------------------------
--登陸預存程序
------------------------------------------------------------------------------
------@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